Granting permissions to execute stored procedures

Something I came across recently, was a client who was granting db_owner permissions to a SQL login which was used as part of a web application. The reasoning for this was that they did not want to manually assign execute permissions to each and every stored procedure they create, and so db_owner gave them what they want with the least amount of effort.

My preferred solution is to create a role myself and assign the execute permission, then assign the new role to my user. This saves me from having to assign execute permissions on a stored proc by stored proc basis, and also means my user doesn’t have additional privileges which they do not require.


   1: --Create a new role for executing stored procedures

   2: CREATE ROLE db_executor


   4: --Grant stored procedure execute rights to the role

   5: GRANT EXECUTE TO db_executor


   7: --Assign role to our new login

   8: EXEC sp_addrolemember 'db_executor','SubjectAccessLog_App'    --allows execution of stored procedures

   9: EXEC sp_addrolemember 'db_datareader','SubjectAccessLog_App'  --allows reading of the data in the security DB

  10: EXEC sp_addrolemember 'db_datawriter','SubjectAccessLog_App'  --allows insert of data into tables in the security DB


More on best practices:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s