Granting Execute Access to All Stored Procedures to a Given User

  • Tim Mitchell

    SSCoach

    Points: 15652

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tmitchell/grantingexecuteaccesstoallstoredprocedurestoagiven.asp

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • David.Poole

    SSC Guru

    Points: 75191

    This might work a bit faster

    DECLARE @ProcName sysname ,

    @usercredentials sysname

    set @procname=''

    set @usercredentials='Development'

    WHILE @procname is not null

    begin

    SELECT @procname = min(name)

    FROM sysobjects

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

    IF @procname is not null

    BEGIN

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

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

    END

    END

  • Adam Cruickshank

    Grasshopper

    Points: 13

    Whatever happened to using a good old fashioned cursor?

  • Mike Metcalf

    SSCrazy Eights

    Points: 8763

    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.

  • F Vandeputte

    Old Hand

    Points: 356

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

    EXEC master..xp_execresultset

    N'SELECT ''GRANT EXEC ON '' + o.name + '' 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:

    http://www.vandeputte.org/2005/10/xpexecresultset.html

    Kind regards,

    Frederik

  • TDuffy

    SSCarpal Tunnel

    Points: 4170

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

    Terry

  • David.Poole

    SSC Guru

    Points: 75191

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

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

  • Mike Metcalf

    SSCrazy Eights

    Points: 8763

    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"

    or

    "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)

  • CDJorg

    Old Hand

    Points: 342

    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.

  • dlhatheway@mmm.com

    Old Hand

    Points: 382

    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.

  • Ted Crafton-214367

    SSC Journeyman

    Points: 88

    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.

  • John Scarborough

    Ten Centuries

    Points: 1104

    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
    MCDBA, MCSA

  • Tim Mitchell

    SSCoach

    Points: 15652

    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
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    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_%'

    Thanks.

  • robertm

    SSC Eights!

    Points: 986

    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)

    Select

          @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