SQL – GRANT EXECUTE to all stored procedures

With the help of  SQL Server Management Studio or Transact-SQL, we can grant permissions on a single or multiple stored procedure in SQL. These permissions can be granted to an existing user, database role, or application role in the database.
For the securtity prospective, the grantor must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted. It requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure.

SQL Server 2005 has been introduced the ability to grant database execute permissions to a database principle as shown below:

----Grant permission at the database scope ----Implicitly includes all stored procedures in all schemas GRANT EXECUTE TO [MyDomain\MyUser] GO

In the above script, we don't have to explicitly grant permissions per stored procedure and user will get the permission by default.

But sometimes, we need to give grant execute permission to all stored procedures explicitly to some specific users. For this, there is a very simple way to include granting execute permissions on only that stored procedures other than anther data components. 

----Grant permission at the database scope ----Explicitly on the exiting stored procedures in all schemas GRANT EXECUTE ON [Stored Procedure Name] TO [User Name] GO

We need to get all the existing stored procedure in your database and then grant execute permission on them one by one as shown below: 

USE [KEY] GO ---- declare local variables DECLARE @StoreProcedure VARCHAR(200) ---- User Variable to store the User Id which need the permissions DECLARE @User VARCHAR(50)='', @Msg VARCHAR(500)='' ---- Set User Name SET @User='msbi_datatools' PRINT '******************************************************' PRINT '------START GRANT EXECUTE PERMISSIONS FOR '+ @User +'--' PRINT '******************************************************' ---- A cursor to get all the stored procedures from the sys.object table DECLARE CURPROC CURSOR FOR SELECT NAME FROM SYS.OBJECTS WHERE TYPE='P' ---- Open Cursor OPEN CURPROC FETCH NEXT FROM CURPROC INTO @STOREPROCEDURE WHILE @@FETCH_STATUS=0 BEGIN ---- set the execute permission on each store procedure EXECUTE ('GRANT EXECUTE ON '+@StoreProcedure+' to '+@User); SELECT  @Msg=DTP.[NAME] +' : ' +OBJ.[NAME] +' : ' +DP.PERMISSION_NAME+' : ' +DP.STATE_DESC FROM SYS.OBJECTS OBJ ----- data componets permission master INNER JOIN SYS.DATABASE_PERMISSIONS DP ON DP.MAJOR_ID = OBJ.OBJECT_ID ----- data components principal master INNER JOIN SYS.DATABASE_PRINCIPALS DTP ON DP.GRANTEE_PRINCIPAL_ID =DTP.PRINCIPAL_ID WHERE OBJ.[TYPE] = 'P' -- STORED PROCEDURE AND DP.PERMISSION_NAME = 'EXECUTE' AND DP.STATE IN ('G') AND DTP.[NAME] =@User AND OBJ.[NAME] =@StoreProcedure ---- print actual permission PRINT @Msg FETCH NEXT FROM CURPROC INTO @StoreProcedure END ---- Close Cursor CLOSE CURPROC ---- Deallocate cursor after use DEALLOCATE CURPROC; PRINT '**************************************************************' PRINT '~~~~~END GRANT EXECUTE PERMISSIONS FOR '+ @User +'~~~~' PRINT '*********************************************************'

From the Standard bar, click New Query then Copy and paste the above example into the query window and click Execute. After executing the above script, the user will get the grant execution permission on the requested stored procedures only.

Limitations and Restrictions - We cannot use SQL Server Management Studio to grant permissions on system procedures or system functions.

Learn more on another features of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL – Stored Procedure Vs Functions
9. SQL - Merge Statement
10. SQL - Functions
11. SQL - Cursors