Grant Execute All Stored Procedures


Here I have tried to explain how we can provide permission to execute all the stored procedure in particular database. In SQL Server 2000 has fixed database roles such as db_datareader and db_datawriter that allow a user read or write access respectively to all the table is a database, no such role exists for the execution of stored procedures (a db_executor role if you will).

This article describes how to grant execute permission to all stored procedures in a database to a specific user or role in both SQL2000 and SQL2005..
In SQL server 2000

Query Analyzer and copy and paste the results of below query into a query window and execute them..


SELECT 'grant exec on ' + Quotename(routine_schema) + '.' + Quotename(routine_name) +
' TO user_name' FROM information_schema.routines WHERE Objectproperty(Object_id(routine_name), 'IsMSShipped') = 0 

 ———————————————————————

In SQL 2005 create role and then grant permission….

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

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

——————————————————————–

As another example: Execute the following statement to give John (user name) the EXECUTE permission for the pr_name (procedure name).

 
GRANT EXECUTE ON procedure_Name TO John;

——————————————————————-

The result of a following example grants EXECUTE permission to testuser for all user created procedures


DECLARE @username VARCHAR(255)

SET @username = 'testuser'

SELECT 'grant exec on ' + QUOTENAME(routine_schema) + '.' +
       QUOTENAME(routine_name) +
              ' TO ' + @username
FROM   information_schema.routines
WHERE  OBJECTPROPERTY(OBJECT_ID(routine_name), 'IsMSShipped') = 0
       AND routine_type = 'PROCEDURE' 

The result of the following example grants EXECUTE permission to testuser for all user created functions.


DECLARE @username VARCHAR(255)

SET @username = 'testuser'

SELECT 'grant exec on ' + QUOTENAME(routine_schema) + '.' +
       QUOTENAME(routine_name) +
              ' TO ' + @username
FROM   information_schema.routines
WHERE  OBJECTPROPERTY(OBJECT_ID(routine_name), 'IsMSShipped') = 0
       AND routine_type = 'FUNCTION' 

INFORMATION_SCHEMA.ROUTINES gives all the information about procedures and functions on current database.

OBJECTPROPERTY function returns property of the given objectid and propertyname.

OBJECT_ID gives the object id of given object name.

IsMSShipped property determines whether object created during installation of SQL Server.
By checking IsMSShipped property to 0 reveals that object are created by user and not system objects.

QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid identifier which should used when contructing dynamic SQL as above from user input.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: