You can use SQL Server Database Roles to control access to various parts of your application. Let’s say I have created a .NET application. I have a menu called Administration and I only want certain users to be able to see the menu. One way to do this is to use Active Directory Groups. Another way is to use SQL Server Database roles.
For this example I create a role called MyAppAdmin. Only members of MyAppAdmin should be able to see the Administration menu. After creating the role and assigning appropriate permissions to the role, I want to check, within my .NET application, to see if the logged on user is a member of the MyAppAdmin role. Here’s how to do this:
Create a stored procedure:
CREATE procedure [dbo].[uspTestDatabaseRole]
— Returns 1 if user is a member of the specified role or if user is dbo
declare @Return integer
if USER_NAME() = ‘dbo’
select @Return = 1
select @Return = IS_MEMBER(@RoleName)
In this procedure the variable @RoleName refers to the Database Role you are checking. You can also use it to check membership in a Windows group. You may notice if the person calling the stored procedure is dbo, I return 1 no matter what. You may not want the procedure to work this way so you should modify for your own purposes.
Now all you need to do is call this procedure from your .NET code and it will return 1 if the person is a member of the database role you are checking or if the person is dbo. Otherwise, it will return 0.