Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Simple scripting question

Simple scripting question

Simple scripting question

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!

RE: Simple scripting question

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: stored proc to copy a users permissions for a new user for information on how to write this script.

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close