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.

Example:

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

   2: CREATE ROLE db_executor

   3:  

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

   5: GRANT EXECUTE TO db_executor

   6:  

   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

Result:

More on best practices:

https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s