Friday, September 28, 2007

How granting execute permission on stored procedures made easier in SQL Server 2005

In SQL Server 2000, a common problem was faced by developers who design their data access to depend on stored procedures, SQL server had db_datareader and db_datareader roles. But no such db_executer role.
This caused trouble as the user used to execute stored procedure had to be granted execute permission on every stored procedure in the database. This can be automated using scripts that grant execute permissions to the required user. But imagine how much time you'll spend debugging your application after creating a new procedure just to discover that you forgot to run the script.
Another easy and dirty way was to add the user used by the application to the db_owner group and make all procedures owned by the dbo. No need to talk about why this is bad.

In SQL Server 2005, the concept of permissions, securables and schemas ,were introduced.
now you can have it all done using one statement. Assuming you have a user test and a schema named HR, test can have execute permissions on all stored procedures belong to HR schema:


Now if you (as dbo) create a procedure like:
CREATE proc HR.GetAccData
Select * from dbo.acc

The used test will have execute permission on the HR.GetAccData procedure.