October 29, 2012 at 10:33 am
I want to grant EXEC permissions to all Stored Procedures in all User Databases.
EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'
I do not want to GRANT EXEC Permissions to Stored Procedures in System Databases.
I suppose that I could easily do this with a cursor or a WHILE LOOP?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2012 at 11:48 am
Use a cursor for that. It's one of the few things they're actually good for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2012 at 2:01 pm
If all the stored procedures are in the same schema you could grant execute on the schema like so.
GRANT EXECUTE ON SCHEMA::[dbo] TO [UserOrRoleName]
October 29, 2012 at 2:06 pm
JeremyE (10/29/2012)
If all the stored procedures are in the same schema you could grant execute on the schema like so.
GRANT EXECUTE ON SCHEMA::[dbo] TO [UserOrRoleName]
but that would grant execute on functions as well...not sure if it's relevant or not.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply