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 * from sys.database_principals 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');
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