July 7, 2010 at 12:19 am
I have a table, lets call it "Employee" which has an insert,update,delete trigger on it (to insert any changes into an audit table).
Security is managed by AD groups as users, so if a user belongs to two groups, I assume that they inherit the lesser permissions if they differ at all.
However, I have two particular groups, one has SELECT, INSERT and DELETE permissions, the other has SELECT and UPDATE.
What I would like to know is whether the UPDATE permission group would also be able to insert or delete?
July 7, 2010 at 12:42 am
If a user belongs to both groups, he could do all DML statement on the table. When a user belongs to more then 1 group, he’ll get the permissions that were assigned to all of the groups. This means that if group A has only select permissions, and group B has only update permissions, if I’m a member of both groups, I’ll be able to do both – update and select. There could of course be a conflict between 2 groups. A conflict is when one group has permission to do an operation, but another group has deny permission on that same operation. In case of conflict, the deny permission will be the effective one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 3:29 pm
Thanks for the reply, but what I wanted to know is whether 'update' also includes 'insert' and 'delete', especially when you have a trigger (i/u/d) on the table, as an update is a delete then an insert. (you can see this when you update a row, and the audit table shows a record inserted from the 'deleted' and another from the 'inserted' tables.
Basically my issue is that some users have access to the table via MS Access (woeful to let this happen, I know, but that's the way it is), and those with update only access should not be able to insert or delete.
July 8, 2010 at 3:27 am
niall.baird (7/7/2010)
Thanks for the reply, but what I wanted to know is whether 'update' also includes 'insert' and 'delete', especially when you have a trigger (i/u/d) on the table, as an update is a delete then an insert. (you can see this when you update a row, and the audit table shows a record inserted from the 'deleted' and another from the 'inserted' tables.
Update permission doesn’t include insert and delete. Each operation needs its own permission. If the users have only update permissions, then they can only update the record but they can select, insert or delete from your table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply