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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply