Tuesday, May 01, 2007

GRANT permission to ALL stored procedures

This is one of the other very common question which I get from many of my blog readers / dotnetspider users.

Hi Vadivel,

I have created a fresh login in my SQL Server 2005 database. Now I want to grant that newly created login permission to execute any / all stored procedure within that database. Can you tell me how to do this in one shot? As of now, I am writting GRANT statement for all individual Stored procedures name manually :(

Regards
xxxxxx

Find above one of the recent mail which I received from one of dotnetspider user. I thought I would write a sample and blog it for benefit of all those people who are having similar requirement. So is this post :)

Solution:

Declare @strUserName sysname
Set @strUserName = 'Support'
Select 'Grant exec on [' + Routine_Schema + '].[' + Routine_Name + '] TO [' + @strUserName + ']' from Information_Schema.Routines Where Routine_Type = 'Procedure'


Now in the result pane the GRANT statement for all stored procedure would have been created something like this:

Grant exec on [dbo].[SampleSp_UsingDynamicQueries] TO [Support]

Just copy the whole result pane and execute it at one shot.

No comments: