Wednesday, February 03, 2010

Allowing a SQL Server user to execute stored procedures

There doesnt seem to be a straightforward way to do this using the GUI in SQL Server Management Studio.

I like managing users using roles and the following method is an easy way to provide a user the permission to execute all stored procedures in that database.

You create a role called db_executor and grant the role Execute priviledges. You then assign the user that needs the ability to execute stored procs to the db_executor role.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

I am not a DBA – so I am not sure if this is not a good practice (because you end up giving execute permissions on all stored procs to all users assigned to that role). Leave a comment if you know otherwise.

No comments: