October 1, 2007 at 7:55 am
Hi,
Thinking about all the posts I have seen (mostly during SS2k time), about different ways of solving a potential problem in GRANTing EXECUTE to a specific user/group, I am wondering if I have stumbled across something new.
BOL:
GRANT { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ class :: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]
(especially the NONmandatory part [ ON [ class :: ] securable ])
Testing:
GRANT EXECUTE TO myUser
GO
EXECUTE sp_helprotect null, 'myUser'
GO
Gives:
Owner Object Grantee Grantor Protect Type Action Column
. . myUser dbo Grant Execute . [/font]
And enables the user 'myUser' to execute any stored procedure in the current database.
The '.' in the Object column seems to indicate ALL.
Has anyone used this approach for enabling a user/group to be able to execute all SPs?
Regards,
Hanslindgren
October 2, 2007 at 10:07 am
To give execute privileges to all procedures, I give execute rights to a DB Role at a schema level then assign users to that role... but database level should work just as well.
David
October 3, 2007 at 9:57 am
Hans Lindgren (10/1/2007)
Has anyone used this approach for enabling a user/group to be able to execute all SPs?
The recommendation is to do so at the schema level. The reason for this is if you ever have to set up a stored procedure you don't want the user to execute, you now have to go through and redefine all the security at the schema level, undo the database level permission, and then proceed. By doing it at the schema level, unless you're creating objects under a brand new schema, the user will be able to execute all stored procedures that are part of any schema you give EXECUTE rights against, whether they are changed or created new.
K. Brian Kelley
@kbriankelley
October 4, 2007 at 1:47 am
Good point!
March 30, 2025 at 8:34 am
Find below script to grant execute permission to user to all stored procedure in Databases
---- 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='[Corp\User_Name]'
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 '*********************************************************'
March 30, 2025 at 8:35 am
Find below script to grant execute permission to user to all stored procedure in Databases
---- 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='[Corp\User_Name]'
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 '*********************************************************'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply