April 20, 2011 at 11:24 pm
Looking for someone to point me in the right direction.
We have recently switched to using Active Directory groups to control database permissions (for development and operations) on a server with hundreds of databases. Somehow users in these groups are still able to alter structure and data in databases where it appears they should only have db_datareader access. Looking at the user mapping for the AD group only shows db_datareader and public checked (in SERVER\Security\Logins). DATABASE\Security\Users only shows db_datareader in user roles and nothing is checked in "Schemas owned by this user".
I am pulling my hair out trying to figure out how the users in this AD group are still able to edit the data/structure directly from SSMS. Is there anything that can be run that looks at all dimensions of a users permissions on a given server or database? Everything I have discovered so far in my searches hasn't helped me get to the bottom of it.
Any suggestions to help me track this down would be greatly appreciated, something that can provide a comprehensive listing of all permissions for a given user?
Thanks.
April 21, 2011 at 5:16 am
Check the server roles. Sounds like they might be sys admin on the box.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2011 at 3:25 am
Thanks for taking the time to reply, Grant.
That was one of the first things I checked. I had also run a bunch of security principal type scripts I had found from research, but nothing I found would yield a complete picture of a login's permissions, i.e. all it's relationships to other accounts that would cause permissions to behave differently than what it appeared they should have by looking at the obvious.
Here is what I discovered for any others facing similar issues:
1. The users in question were inconspicuously associated with an Active Directory group that was a member of Domain Admins
2. The configuration on the SQL 2005 server appeared to implicitly associate Domain Admins with the BUILTIN\Administrators server login. I'm thinking this is probably a default server configuration.
3. The BUILTIN\Administrators account was a member of the Server Role sysadmin (again a default configuration)
I removed the sysadmin Server Role association from the BUILTIN\Administrators account (after having to change dbowner for a few databases where it was the owner). This solved the issue. A few articles I came across recommended removing the sysadmin privileges of the BUILTIN\Administrators account for exactly this reason.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply