Monday, October 19, 2015

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

No comments:

Post a Comment