CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlssms
p.campbell
p.campbell

Reputation: 100657

Quick way to grant Exec permissions to DB role for many stored procs

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:

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?

Upvotes: 25

Views: 103421

Answers (5)

Rabbit
Rabbit

Reputation:

Easiest way is to:

GRANT EXECUTE ON myproc TO x

where x =

  1. SQL User
  2. Role
  3. AD Group/Account

Upvotes: 6

ZooZ
ZooZ

Reputation: 981

Simply update the dbo schema and set add an EXECUTE permission on this schema to the desired user/role.

Upvotes: 2

user1090467
user1090467

Reputation: 

USE database_name;
GRANT EXECUTE TO [security_account];

Don't forget the brackets :)

Upvotes: 37

RBarryYoung
RBarryYoung

Reputation: 56785

This should do it:

CREATE PROC SProcs_GrantExecute( 
    @To AS NVARCHAR(255)
    , @NameLike AS NVARCHAR(MAX)
    , @SchemaLike as NVARCHAR(MAX) = N'dbo'
    ) AS
/*
 Proc to Authorize a role for a whole bunch of SProcs at once
*/
DECLARE @sql as NVARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + '
 GRANT EXECUTE ON OBJECT::['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+'] TO '+@To+';'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE @NameLike
 AND ROUTINE_SCHEMA LIKE @SchemaLike

PRINT @sql
EXEC(@sql)

This is Injectable as heck, so keep it for Admin use only.

I just want to add that Remus's suggestion of using schemas is the preferred approach, where that is workable.

Upvotes: 12

Jack Marchetti
Jack Marchetti

Reputation: 15754

you can do this, however I'm not entirely sure how secure this is.

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Upvotes: 20

Related Questions