Finding out the windows group by virtue of which a user is able to access a database in sql server?
- by Raghu Dodda
There is a SQL Server 2005 database with mixed-mode authentication.
Among others, we have the following logins on the server: our-domain\developers-group-1, and our-domain\developers-group-2 which are AD groups.
The our-domain\developer-group-2 is added to the sysadmin role on the server, by virture of which all domain users of that group can access any database as SQL Server implictly maps the sysadmin role to the dbo user in each database.
There are two users our-domain\good-user and our-domain\bad-user
The issue is the following: Both the good-user and the bad-user have the exact same AD group memberships. They are both members of our-domain\developers-group-1 and our-domain\developers-group-2. The good-user is able to access all the databases, and the bad-user is not. The bad-user is able to login, but he is unable access any databases. By the way, I am the good-user.
How do I go about finding out why?
Here's what I tried so far:
When I do print current_user, I get dbo
When I do print system_user, I get my-domain\good-user
When I do select * from fn_my_permissions(NULL, 'SERVER'), I see permissions.
But if do execute as user='my-domain\good-user'; select * from fn_my_permissions(NULL, 'SERVER'), I dont see any permisisons.
And When I do, execute as user='my-domain\bad-user'; select * from fn_my_permissions(NULL, 'SERVER'), I dont see any permisisons.
Also, I was wondering if there is a sql command that will tell me, "hey! the current database user is able to access this database because he is a member such-and-such ad-group, which is a login that is mapped to such-and-such user in this database".
