SQL Server Performance Forum – Threads Archive
Permission conflict
Hi What permissions are granted in the following situation as i have heard two completely different answers? A user is assigned to two seperate window login groups which have been included as logins in SQL Server. Two user accounts are set up in a database that uses these two logins but different permissions are granted on the different user accounts e.g. one user account has only SELECT permission but there other has EXEC permissions also. What would the permissions of the user be in this case; will the user be granted the highest most permissions so there can EXEC as well as SELECT or will they be granted the lowest permissions so they can only SELECT? Thanks for your helpCE
The user will be granted any permission that is granted to any role to which he belongs, that is not denied to any other role to which he belongs, for the same object. So if Role_1 is granted EXEC permission, and Role_2 is denied EXEC permisison on the same object, and the user belongs to both roles, then he cannot EXEC the object in question. Note that there is a difference between not granting a permission (passive), and denying a permission (active). So if Role_1 is granted EXEC permission, and Role_2 is not granted or denied EXEC permission on the same object, and the user belongs to both roles, then he can EXEC the object. Not sure what kind of object would have both EXEC and SELECT permissions, but that’s my problem.
quote:Originally posted by Adriaan
Not sure what kind of object would have both EXEC and SELECT permissions, but that’s my problem.
As far as I know, there is no such object. But, anyone, please correct me if I am wrong.
However, user-defined functions depending on their type (scalar or table-valued) have either SELECT or EXEC permission.create function foo()
returns table
as
return (select * from sysobjects)
go grant select
on dbo.foo
to ADMIN
go select * from dbo.foo() — create function bar()
returns int
as
begin
return 1
end
go grant exec
on dbo.bar
to ADMIN
go select dbo.bar()
—
Marek Grzenkowiczreturns table
as
return (select * from sysobjects)
go grant select
on dbo.foo
to ADMIN
go select * from dbo.foo() — create function bar()
returns int
as
begin
return 1
end
go grant exec
on dbo.bar
to ADMIN
go select dbo.bar()
>>><br />So if Role_1 is granted EXEC permission, and Role_2 is not granted or denied EXEC permission on the same object, and the user belongs to both roles, then he can EXEC the object.<br />>>><br /><br />this shouldn’t quite be the case… a deny overrides any other permission granted… so a deny exec from one group and a grant exec from another ought to result in a deny if the user belongs to both… This is the way any permission based system should work… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Just like as in NTFS if you deny a group from reading a file and grant another then if you belong to both groups you won’t be able to read the file. Try deny select on tablex from public and see how you get on as a dbo <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Cheers<br />Twan
<blockquote id="quote"><font id="quote" size="1" face="Verdana, Arial, Helvetica">quote:<hr height="1" id="quote" noshade="noshade"><i>Originally posted by Twan</i><br><br>This is the way any permission based system should work… <img src="http://sql-server-performance.com/community/emoticons/emotion-5.gif" alt=";-)"><hr height="1" id="quote" noshade="noshade"></font></blockquote>And it does.<br><br>Only the sentence you quote doesn’t state this clearly. But I guess what Adriaan meant was <i>’…and Role_2 is neither granted nor denied EXEC permission…'</i>.<br><br>–<br><br>Marek Grzenkowicz<i><br></i>
Sometimes totally clear logic gets in the way of clear writing – of course I should have said ‘neither granted nor denied’. [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]
]]>