July 11, 2005 at 12:17 pm
I am slightly confused how different permissions on the same object work?! I mean, let say user UserA has “Select” permission on table tableA. But UserA is a member of Windows group groupA that was added to database role RoleA with “Update” permission on tableA. How Sql Server will manage this case, I mean, what kind of permission on tableA to use, e.g. when UserA is using QA or an application with Windows authentication? Thanks
July 11, 2005 at 12:21 pm
Like in Windows NTFS permissions, SQL Server will aggregate the permissions for he user. So if user A has SELECT permissions on table A explicitly assigned to the user and UPDATE permissions through groupA -> roleA, the final permissions for the user will be SELECT and UPDATE.
BTW, the trump is still DENY. If a user has a DENY anywhere, it blocks, just like with NTFS.
K. Brian Kelley
@kbriankelley
July 11, 2005 at 12:30 pm
Thanks, Brian. Just to confirm that I've got you right- Sql Server will check every permission User has (all roles, etc) and aggregates them regardless what User is using- QA, EM, application, etc? Thanks
July 11, 2005 at 12:33 pm
Correct. Regardless of application, when a user logs in those rights will be granted.
If you need to grant permissions differently per application, look at application roles. Those really only work with non-MS applications as you have to call a stored procedure to activate an application role once the user logs in.
K. Brian Kelley
@kbriankelley
July 11, 2005 at 12:46 pm
Thank you- makes complete sense!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply