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 A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
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:
Post a Comment