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 '*********************************************************'
|
No comments:
Post a Comment