Granting Execute Access to All Stored Procedures to a Given User

  • Comments posted to this topic are about the content posted at

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant | @Tim_Mitchell |
    ETL Best Practices

  • This might work a bit faster

    DECLARE @ProcName sysname ,

    @usercredentials sysname

    set @procname=''

    set @usercredentials='Development'

    WHILE @procname is not null


    SELECT @procname = min(name)

    FROM sysobjects

    WHERE xtype='P' and name > @procname and name not like 'dt_%'

    IF @procname is not null


    EXEC('GRANT EXECUTE ON ' + @procname+ ' TO ' + @usercredentials)

    PRINT 'Execute permissions granted on '+ @procname+ ' TO ' + @usercredentials



  • Whatever happened to using a good old fashioned cursor?

  • Just to add that it's often much easier in multi-user environments where more than a couple of users need similar access to create a single role and grant permissions to the role, then you can just add the users to the role to save you time.

  • The code below will do the trick with only one line of code:

    EXEC master..xp_execresultset

    N'SELECT ''GRANT EXEC ON '' + + '' TO '' + ''my_user''

    FROM sysobjects o

    WHERE xtype = ''P'' AND [name] NOT LIKE ''dt_%'' ', my_db

    Just replace my_user and my_db with the correct values.

    I posted some more nice examples on xp_execresultset my blog some time ago:

    Kind regards,


  • Yes I'm being picky but doesn't this belong in Scripts rather than articles?


  • "Good" and "cursor" in the same sentence.

    "Old Fashioned" is OK, it implies that something has worked

  • I can't agree more, there's no way that those 2 words should ever appear in the same sentence

    Oh, wait, how about...

    "it's no good, he's used a cursor rather than doing it properly with set based logic"


    "good, said the highly paid consultant, i see that they've used a cursor; i should be able to make things a little faster if you want to pay me some more money"

    (but only for 99.99999 % of cases)

  • This likely won't make any difference when looping through stored procedure names, but it should be noted that "_" is a wildcard, representing any one character. 

    like 'dt_%' would be better written as like 'dt\_%' escape '\' to force the script to treat the underscore as itself.

    Otherwise, great script!  Very useful.

  • I'd vote for creating the "executors" role, adding "execute" permission on all the SPs to that and then adding the user(s) in question to the "executors" role.

    Maybe "executors" would be a good addition to the model db.

    By the way, where's your defensive "is this a real login" check?  Chalk it up to paranoia, if you like, but I'd have a test in there.

  • Everyone is down on Cusors and then they turn right around and use sysobjects in their scripts. 

    Why not use the views Microsoft provided?

    SELECT specific_name FROM information_schema.routines where NOT specific_name like ('dt_%')

    I mean if you are going to do it the proper way, then do it all by the book.

  • We had a situation a while back in which a cursor based solution was faster than a t-sql solution. Note that this was NOT what we were attempting to prove to the developer! Oh well.

    John Scarborough

  • Cursors are like toilet plungers: you hope you don't have to use them, but it's nice to know how just in case.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant | @Tim_Mitchell |
    ETL Best Practices

  • I looked up the bol. What min(name) here do ?

    SELECT @procname = min(name)

    FROM sysobjects

    WHERE xtype='P' and name > @procname and name not like 'dt_%'


  • If you wanted to avoid usiing extended stored prcedures you could do it like this. It's a bit more understandable than multiple nested select statements that are required for xp_execresultset to work properly...


    -- Specify the user account which should be

    DECLARE @login VARCHAR(50)

    SET @login = 'my_user_id'


    DECLARE @sql VARCHAR(max)


          @sql = Coalesce(@sql, '') + 'GRANT EXEC ON [' + [name] + '] TO [' + @login + ']' + Char(13)

    FROM sysobjects WHERE xtype = 'P' AND [name] NOT LIKE 'dt_%'


    EXEC (@sql)



Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply