Database-level permissions for a Windows user with login created for both user and containing AD group?

Question

How does SQL Server handle logins when there is an ambiguity, for instance, logins exist for both the Windows User account and for a AD group that contains this user ? We've had a little issue with permissions in SQL Server 2008 with Windows users from our Active Directory and groups from that AD. I'll try to explain with an example. Imagine a Windows Domain User `DOMAIN\myUser` that belongs to an AD group `DOMAIN\SomeGroup`. In SQL Server, I have 2 databases `SomeAppDb` and `PublicDb`. The goal is that all users that are members of `DOMAIN\SomeGroup` should be able to access `PublicDb`, but only `DOMAIN\myUser` should be able to access `SomeAppDb`. Initially, in SQL Server a Windows login `DOMAIN\SomeGroup` (mapped to the AD group) was created on the instance, and a User was created in the database `PublicDb` with proper role membership, and that worked well, users from group `SomeGroup` could access the data they needed in `PublicDb`. For the needs of a new application, we wanted to…

Accepted Answer

My intuition tells me that when the user accesses the SQL Server instance, SQL Server sees a login the matches the Windows user and ignores the login existing for a group that the user belongs to.

That intuition is not correct. And that is a good thing as the way you want the security setup to work is the way that it should be working; permissions are additive. I just reproduced your setup and creating the Login for the Windows Login did not have any adverse effects on the permissions (i.e. specific DB access) that were assigned to only the Login based on the Windows Group. I am even able to set the default database for the Windows Login-based Login to a database that is only accessible via a mapping related to the Windows Group-based Login. And yes, my test Login was only a member of the `public` server and database roles, and I did verify that any database not explicitly mapped to either the Windows Group-based Login or the Windows Login-based Login was not accessible at all.

So, I am pretty sure that something either changed outside of what has been mentioned or some other config is creating this situation. But first, we probably should be clear about the exact issue. The description of the problem states:

From that moment, myUser could access SomeAppDb as expected, but could not longer access PublicDb

This would have to mean that `myUser` was able to login. However, the user somehow could not change to PublicDb via `USE [PublicDb]`? Or are we talking about something else? Maybe something else as implied by the exact error message:

Cannot open database "PublicDb" requested by the login. The login failed. Login failed for user 'DOMAIN\myUser'

If `myUser` was logged in and simply changing databases, or doing a cross-database query, then there would not be a "login failed" error message. This leads me to suspect that the default database (or the database specified in the connection string) is `SomeAppDb` and as stated, there are no problems there. But then it would have to be a connection string specifying "PublicDb" that was having the issue. If so, would that same connection string, copied and pasted (not retyped) for someone else work? Maybe there is a typeo, even a hidden character, in the connection string that specifies "PublicDb"? The only ways I have been able to reproduce that error is by connecting via SQLCMD while specifying a database that:

- did not exist

- existed, the account had access to, but had square-brackets around the name (e.g. `-d "[PublicDb]"`)

- existed but the account did not have access to (meaning, there are more things to test as noted below ;-)

If not a connection string issue, here are some things to check:

While `DOMAIN\myUser` is logged into SQL Server, `DOMAIN\myUser` should run the following:

```
-- http://msdn.microsoft.com/en-us/library/ms186271.aspx (IS_MEMBER)
SELECT IS_MEMBER(N'DOMAIN\SomeGroup'); 
```

If that Login is really in that Group, then it will return a `1`. If it doesn't, then either:

- `0` means the Login is a Windows Login but not in that Group, so it was removed from the Group, or

- `NULL` means this is not a Windows Login (seems unlikely since the Login name in SQL Server has a `\` which is not a valid character for a SQL Server Login)

While `DOMAIN\myUser` is logged into SQL Server:

- Remove that temporary fix of the `DOMAIN\myUser` User being in `PublicDb`.

`DOMAIN\myUser` should run the following:

```
SELECT HAS_DBACCESS(sd.[name]) AS [HasAccess], *
FROM sys.databases sd
ORDER BY 1 DESC, [name] ASC;
```

Does `PublicDb` show up in the list?

Run the following query:

```
SELECT *
FROM sys.server_principals
WHERE [name] IN ('DOMAIN\myUser', 'DOMAIN\SomeGroup');
```

Check the following fields: `sid`, `type_desc`, and `default_database_name`. Make sure that the "default database" is a really a database. It could be that the value was set incorrect when you initially added the Login for `DOMAIN\myUser`. If nothing else, maybe try setting it to `[master]` to see if that gets around the error. If that works, set it back to `[PublicDb]`.

Have "myUser" log into Windows, go to a command prompt, and run:

```
SQLCMD -E -Q"SELECT DB_NAME(), USER; USE [PublicDb]; SELECT DB_NAME(), USER;"
```

The first row returned should be `SomeAppDb DOMAIN\myUser`. Then a message about switching database contexts. And then they should see `PublicDb DOMAIN\myUser`.

- If yes, then this is definitely not an "db access" issue.

- If no, then either this Login is not part of that Group anymore or something specific is blocking it. In which case, was anything done when adding the specific Login for `DOMAIN\myUser` beyond "a User was created in the database SomeAppDb, with a mapping between the 2." such as denying any server-level or database-level permissions?

Have "myUser" log into Windows, go to a command prompt, and run:

```
SQLCMD -E -Q"SELECT DB_NAME(), USER;" -d"PublicDb"
```

They should get a row returned being `PublicDb DOMAIN\myUser`. If yes, then…

Database-level permissions for a Windows user with login created for both user and containing AD group?

Solution

Problem