Wednesday, January 25, 2012

Grant Execute to Given User - Dynamic Query

declare @username varchar(255)
set @username = 'YourUser'
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 above query with result in all the User Defined Stored Procedures with "Grant Execute Permissions.

Run the resulting commands and 'YourUser' has the Execute Rights :)

No comments: