April 30, 2012 at 8:17 pm
I have a very interesting issue, that I suspect is a bug, but have been unable to confirm, and was hoping someone might be able to shed some light on the subject.
I have a user, whose user account is a member of an Active Directory group (let's call it 'SQL_DB_DBO'). That group has been granted membership in the db_owner role on a particular database.
First, if I review the user mappings for this user at the server level, it shows no access to the database in question, but nonetheless I know he is able to access it. This is confusing, but not a problem, but does suggest to me there was something lost in the thought process.
The true problem is this: As a member of the group that supposedly grants db_owner role, the user is unable to enumerate the permissions on the database objects. However, once I explicitly grant the use membership in the db_owner role, permissions can be viewed (and presumably changed).
We are using SQL Server 2008.
Any insight would be greatly appreciated.
May 1, 2012 at 1:21 am
This is not a bug but the way it is designed. If user gets the role through a role the enumeration is possible at role not at user level.
May 1, 2012 at 8:55 am
Thanks Dev, but as I said, that part really isn't the problem. It's the fact that members of that AD group don't inherit ability to view permissions unless I explicitly grant them the db_owner role in THEIR User Mappings.
But that 'design' is definitely not well thought out. For one, in a complex environment, where a user could be a member of multiple groups, which are in turn assigned permissions via multiple flexible roles, how does an administrator determine the effective permissions of such a user? At the very LEAST, It would be much better if SSMS did enumerate the permissions in the User Mappings, but had them greyed out, or some other indicator that the permission is in effect, but enabled or inherited from elsewhere.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply