Sql-server – Quick way to grant Exec permissions to DB role for many stored procs

sql-serversql-server-2005ssmstsql

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.

SQL Management Studio

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.

Question:

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?

Best Solution

USE database_name;
GRANT EXECUTE TO [security_account];

Don't forget the brackets :)