question

sanasqldba avatar image
sanasqldba 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.
stored-proceduresdynamic-sqlprocdefinition
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.