SQL to Grant EXECUTE permission to all Procedures and Functions

   Tweet

Syntax for Granting EXECUTE permissions on system objects such as stored procedures, extended stored procedures, functions, and views is

GRANT EXECUTE ON <<object_name>> TO <<user_name>>;

The following example grants EXECUTE permission on sp_test(procedure name) to John (user name).

GRANT EXECUTE ON sp_test TO John;

The result of the 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.

   Tweet