How to create sql server database role and manage its members

In this blog post we will see how we could create a flexible sql server database role and assign to users. If you are not familiar with SQL Server roles check this post first. To demonstrate this we will create a simple procedure.

-- Create a test Stored Proc
create proc spContact
as
     select top(20) * from tContact;
go
 
-- Check database principal JasonNigatu
select&nbsp; * from sys.database_principals&nbsp; where name like '%JasonNigatu';

Let us start another server instance using runas and check what permissions JasonNigatu has

select * from sys.fn_my_permissions(null,'Database');

sql server database role

We can see that JasonNigatu only has a select permission in the database and if we try to execute the stored procedure as JasonNigatu it will fail.

-- Check JasonNigau has permissions to execute spContact
execute as user = 'HOMESERVER\JasonNigatu';
go
 
exec spContact
go
 
revert; --exit impersonation 
go

Now let us create a database role that has execute permission.

-- Create a role that has permissions to execute spContact
create role dbSpContact_ExecRole;
go
 
grant execute on SpContact to dbSpContact_ExecRole;
go

Assign JasonNigtu to be a member of our new role.

-- Make JasonNigatu member of dbSpContact_ExecRole role
sp_addrolemember 'dbSpContact_ExecRole', 'HOMESERVER\JasonNigatu';
go

if we try to execute the stored procedure now as JasonNigatu it sure does execute

-- Let check if JasonNigatu can execute the stored proc again
execute as user = 'HOMESERVER\JasonNigatu';
go
 
exec spContact
go
 
revert;
go

-- Let us say after some time in the future we want to denay JasonNigatu from
-- being able to execute our stored proc we could do this by
 
exec sp_droprolemember 'dbSpContact_ExecRole', 'HOMESERVER\JasonNigatu';
go