Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures.
When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role.
Here are the steps to apply permissions:
- select the object that you want to grant/deny permissions in the list of Securables.
- navigate to the list of Explicit Permissions below.
- select the Grant or Deny checkbox as appropriate.
Repeat the above for n objects. Fire up some music to keep yourself entertained while doing this for 100+ objects! There's got to be a better way! It's a clickfest of major proportions.
Is there a faster way to perform this task using SQL Server Management Studio 2005? Perhaps another GUI tool (preferably free)?
Any suggestions for creating T-SQL scripts to automatically perform this task? i.e. create a table of all stored procedure names, loop, and apply the exec permissions?