asked
Grant View Definition for all stored procedures of a login
In an attempt to automate a few features of DB Administration I have come up with the idea of witting a stored procedure to give view definition permission to all the user defined stored procedures of a Windows login user. Instead of manually going to each stored proc to grant them view definition. I need help with creating a dynamic stored proc for this purpose. ----- algorithm--- 1.get input 2.check if the db exists 3. get the user login for whom we need permission 4. check if the login exists 5. grant view definitions for all the stored procs on the login 6. end ------ This stored proc should be able to run on any DB. I am a SqL DBA . kindly help met turn this algo to code.
I do it for all stored procs in on fell swoop. I created a database role called "DB_ViewAllProcs" and gave it the privs. I just have to make the new Windows User (or group) a member of that database role and Bob's your uncle.
Check out the examples in this blog post. Not sure it is exactly what you want but it may provide some inspiration.
http://sqlserverimpressions.blogspot.com/2008/10/grant-permissions-to-view-stored.html
15 People are following this question.
