Rotating Header Image

db_Executor Role – updated

In a passing conversation the question about how you grant execute to all stored procedures to a user came up. In SQL 2005 the ability to grant the execute permission at the database level was introduced. Instead of having to build some 'smarts' into a script to cycle through all your objects, etc you can now solve this problem as easy as:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'username'

So this is an “all or nothing” script so if you're wanting to be selective with which stored procedures you want to grant the permission to than this is not the solution for you… but head on over to facility9 for a look at how you can grant execute on a “pattern” of stored procedures.

UPDATE: As pointed out by K Brian Kelley you might end up with more work in doing this if later down the road there is a stored procedure that you don't want the user to be able to execute. He suggests (and how can you NOT trust an MVP) granting execute not at the database level but at the schema level for more control over what the user can and cannot execute… very sound advice… Thanks!!



Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

One Comment

  1. I prefer the batch-import feature of Storehouse, where you preselect a bunch of the photos and video clips you want to use and it pulls all of them into the project timeline at once.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.