Simple scripting question

[SBendBuckeye]

Hello all,

We are running SqlServer 2005. For my current project, there is a group defined to which we need to Grant Execute permission for all select stored procs. I have been manually adding the group by right clicking the stored proc and then choosing Properties, Permissions, etc.

I'm thinking there has to be a way to script this and make it faster and less error prone if I forget one.

A. Can I script this process to pass in the StoredProc name and then have it add the group for me?

B. Can I write a script like A that only adds the group if it does not already exist?

C. Can I write a script that returns this information for every stored proc in the database?

Sorry if these are really obvious questions. Thanks in advance for any ideas and/or suggestions!

 

[mrdenny]

A. Yes, put you have to use dynamic SQL. It's better to simply write the grant command.

GRANT EXEC ON StoredProcedure TO UserOrRole

B. You don't need to worry about adding rights that already exist. If the right already exists on the role or user it will simply ignore the new right without reporting an error.

C. Check this thread thread962-1324602 for information on how to write this script.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com