Tuesday 13 April 2010

[Tip] Grant Executor Permissions on All Stored Procedures

This tip works on SQL Server 2005 or above

Most people say that all sql code should be kept separate from your application in the database layer as stored procedures. Whenever I have done this in the past I have had to go into each stored procedure and set the execute permissions for the users. This is time consuming and error prone.

I realised that there must be an easier way of setting the execute permissions so that it only had to be set once for the database after a bit of searching I found the answer.

You just have to run the following sql code on the database.

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

This code creates a new role called 'db_executor' then grants executor permissions to the role.

After running this code you can add a user to the new role by running the following sql code.

EXEC sp_addrolemember 'db_executor', 'sqlusername'

Important Note:
The only draw back with this method of assigning execute permissions is that the user will get permissions to execute any stored procedure already created or created in the future.

No comments: