Granting Execute Access to All Stored Procedures to a Given User
-
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 -
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
-
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 '' + 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
-
Yes I'm being picky but doesn't this belong in Scripts rather than articles?
Terry
-
"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"
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)
-
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
MCDBA, MCSA -
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 -
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.
-
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 15 total)
You must be logged in to reply to this topic. Login to reply