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 give explicit access for db SomeAppDb to user DOMAIN\myUser, while still allowing access to PublicDb. We therefore created in SQL Server a Windows login for DOMAIN\myUser, and a User was created in the database SomeAppDb, with a mapping between the 2.

From that moment, myUser could access SomeAppDb as expected, but could not longer access PublicDb, and we had an error like :

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

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.

One approach is to explicitely add access on the db PublicDb for user myUser, but I would rather avoid that solution as it forces to update PublicDb everytime we want to give access to new users which is exactly what we were trying to avoid initially ... (we did that as a temporary fix, hoping to find a better option).

Has anybody else encountered this issue ? is there a better approach ?

thanks in advance

Answer 1

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:

1. 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)

2. While DOMAIN\myUser is logged into SQL Server:

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

   2. 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?

3. 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].

4. 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?

5. 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 this is definitely not a "login" issue.

6. An easy test would be:
   1. Create a new Active Directory account
   2. Make this account a member of 'DOMAIN\SomeGroup'
   3. Log into Windows as this account
   4. Connect to SQL Server
   5. Try to access both databases. That account should be able to access only PublicDb
   6. Disconnect from SQL Server
   7. Create a Login in SQL Server for that test Windows account
   8. Create a User (no extra options) in SomeAppDb for that Login (e.g. CREATE USER [DOMAIN\myUser] FOR LOGIN [DOMAIN\myUser])
   9. Connect to SQL Server
   10. Try to access both databases. That account should now be able to.
7. One last thing to test would be to remove all of the pieces that appear to have been the cause of this error. So get rid of the DOMAIN\myUser User in SomeAppDb and then get rid of the DOMAIN\myUser Login. If one of those things really did cause this error then at this point DOMAIN\myUser should be able to access PublicDb again.
8. What other Windows Groups is the Windows Login a member of (at least any that would have a Login)?
9. What "role membership" were you speaking of? Are these builtin Roles or custom Roles?

---

P.S. Most of the related posts, floating around out there, that deal with this same error end up either creating a SQL Server login to use or adding the Windows-based Login to the "db_owner" role. I think that both of those solutions are cop-outs and that computers are not arbitrary in their behavior so we just need to find the cause.