Finding out the windows group by virtue of which a user is able to access a database in sql server?

Question

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".

Answer 1

If you have the relevant permissions within Management Studio, this shouldn't take too long to work out. It sounds like the bad-user AD group has limited permissions within SQL Server.

You need to check the settings in Security in the GUI, and check the mappings for each of these AD groups - clicking on the databases to see what permissions they have on each database.

Answer 2

Is it possible that you simply have implicit permissions as the creator/owner of the objects, yet no explicit permissions have been granted on the AD groups/roles or to your user individually?