September 9, 2011 at 7:17 am
Hello,
I am experiencing a strange problem. We have a database that had been migrated from 2000 or 2005 to 2008 about a year ago.
User A is a member of: db_datareader, db_datawriter, db_owner, db_ddladmin, db_accessadmin, db_owner, and db_securityowner.
User A's default schema is his own(UserA).
User A's login is only a member of public at the instance level.
All tables are in the dbo schema and owned by dbo.
I would have thought that by being in db_datawriter, that User A would have insert/update permissions on any tables. The problem is not isolated to UserA in this database. Some users that are sysadmin, do not show as having insert/update effective permissions.
Some users with same role membership have different effective permissions.
Some users within the dbo schema with same role membership have different effective permissions.
Any suggestions on where to look? Am I wrong in my understanding of db_datawriter? And for those with effective insert/update permissions, I don't see any explicit GRANTs either. I've looked as best as I know how to for any possible DENY statements. My next step/hope is that Profiler may shed some additional light.
Thanks,
Hassle2
September 9, 2011 at 11:14 am
Hassle2 (9/9/2011)
Hello,I am experiencing a strange problem. We have a database that had been migrated from 2000 or 2005 to 2008 about a year ago.
User A is a member of: db_datareader, db_datawriter, db_owner, db_ddladmin, db_accessadmin, db_owner, and db_securityowner.
User A's default schema is his own(UserA).
User A's login is only a member of public at the instance level.
All tables are in the dbo schema and owned by dbo.
I would have thought that by being in db_datawriter, that User A would have insert/update permissions on any tables.
They do. That's the idea of the Fixed Database Role. Membership in the Role allows the User to insert, update and delete data from all user-defined tables.
The problem is not isolated to UserA in this database. Some users that are sysadmin, do not show as having insert/update effective permissions.
What do you mean by "do not show as"? If a user has permissions to insert into a table via a Database Role then you may not see an explicit GRANT permission on the table for every User in that Role. How are you checking? Is the User getting a permission denied error when trying to insert data or are you just auditing security at the moment?
Some users with same role membership have different effective permissions.
Some users within the dbo schema with same role membership have different effective permissions.
Can you please elaborate on your definition of "effective permissions"?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2011 at 11:25 am
Hi,
Thanks for the response. The users were getting a permission denied on the object error. I found the culprit and feel silly about it taking this long to find it.
Someone had added domain users to db_denydatawriter. And the users with sysadmin were able to work. I had noted that bit wrong on my paperwork. So... domain users group was taking precedence with the denydatawriter over the specified user.
Thanks though!
September 9, 2011 at 11:46 am
Hassle2 (9/9/2011)
And the users with sysadmin were able to work. I had noted that bit wrong on my paperwork.
Because sysadmins are by definition given every permission in the DB, when someone is a member of the sysadmin group permissions checks are not done
So... domain users group was taking precedence with the denydatawriter over the specified user.
No, Deny always takes precedence over Grant. The point being you can grant permissions to a group, then deny them to individuals or vis versa and the deny always wins.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 11:58 am
GilaMonster (9/9/2011)
Hassle2 (9/9/2011)
And the users with sysadmin were able to work. I had noted that bit wrong on my paperwork.Because sysadmins are by definition given every permission in the DB, when someone is a member of the sysadmin group permissions checks are not done
I realize and know that syadmins would be exempt from the denydatawriter.
GilaMonster (9/9/2011)
Hassle2 (9/9/2011)
So... domain users group was taking precedence with the denydatawriter over the specified user.No, Deny always takes precedence over Grant. The point being you can grant permissions to a group, then deny them to individuals or vis versa and the deny always wins.
I had written down a sysadmin login incorrectly in my log. And also know that Deny overrides Grant. Unless a table is denied and a column is granted (without c2 enabled).
Way too much time spent on an oversight on my part!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply