Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tom Jones
Starting Member
1 Post |
 Posted - 2008-11-03 : 09:28:50
|
| Hello!I've got a user that's in two roles on my SQL Server 2005:- role1- role2I've got a stored procedure that "role2" should be able to execute, so I've granted execute permissions to only "role2" for that one. I have, however, neither granted nor denied permissions on that stores procedure for any other roles.A client happens to be a member of role1 and role2. When trying to execute the aforementioned procedure that's granted execute permissions on "role2", it says execute permissions are denied.Is this because it implicitly denies execute permissions on "role1" and that takes precedence over my granted execute permissions on "role2"? Or is there something else? I'd really like to solve this problem the way described with two roles, since a client here can wear many hats.Best RegardsTom |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-03 : 10:54:11
|
| There's no such thing as an implicit deny, except where the deny is on a higher-level object (deny on a schema affects all the objects in the schema). If Role1 has not been explicitly denied execute rights, it won't affect another role that explicitly grants exec.Permissions in SQL have 3 states. Explicitly granted, explicitly denied and a neutral state where nothing has been set. Only an explicit deny overrules a select.If you take the user (temporarily) out of Role1, can he execute the proc?If you log in to management studio as that user and run the following, what does it return?SELECT * FROM fn_my_permissions('<Stored Proc Name>', 'object');Logged in as a n admin, what does the following return?SELECT pri.name, pri.type_desc, per.permission_name, per.state_descFROM sys.database_permissions per inner join sys.database_principals pri on per.grantee_principal_id = pri.principal_id WHERE major_id = OBJECT_ID('<Stored Proc Name>');--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
User in Multiple Roles Can't Exec Stored Proc