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

Question

How does SQL Server handle logins when there is ambiguity, for example, logins exist for a Windows user account and for the AD group that contains this user?

We had a small permission issue in SQL Server 2008 with Windows users from our Active Directory and groups from this AD. I will try to explain with an example.

Imagine a Windows domain user DOMAIN\myUser , which belongs to the AD DOMAIN\SomeGroup .

In SQL Server, I have 2 databases SomeAppDb and PublicDb .

The aim is that all users who are members of DOMAIN\SomeGroup , can have access to PublicDb , but only DOMAIN\myUser should have access to SomeAppDb .

Initially, the SQL login DOMAIN\SomeGroup was created in the instance in SQL Server (mapped to the AD group), and the user was created in the PublicDb database with the proper membership in the roles, and this worked, users from the SomeGroup group could access the required data in PublicDb .

For the needs of the new application, we want to provide explicit access for db SomeAppDb to the user DOMAIN\myUser , while maintaining access to PublicDb . Therefore, we created a Windows account in SQL Server for DOMAIN\myUser , and the user was created in the SomeAppDb database, with a mapping between 2.

From now on, myUser can access SomeAppDb as expected, but could not get longer access to PublicDb , and we had an error, for example:

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

My intuition tells me that when a user accesses an instance of SQL Server, SQL Server sees that the login is the same as the Windows user and ignores the login that exists for the group to which the user belongs.

One approach is to explicitly add access to db PublicDb for user myUser, but I would prefer to avoid this solution because it forces updating PublicDb every time we want to provide access to new users, which is exactly what we tried avoid initially ... (we did it as a temporary fix, hoping to find a better option).

Has anyone else encountered this problem? is there a better approach?

early

Answer 1

My intuition tells me that when a user accesses an instance of SQL Server, SQL Server sees that the login is the same as the Windows user and ignores the login that exists for the group to which the user belongs.

This intuition is wrong. And that’s good, how you want the security system to work is how it should work; permissions are additive. I just reproduced your setup, and creating a Windows login did not adversely affect the permissions (i.e. Specific DB Access) that were assigned only for logging in based on a Windows group. I can even set a default database for logging in to Windows Login-based to a database that is accessible only through the mapping associated with logging into the Windows Group. And yes, my test login was only a member of the server and public database roles, and I made sure that any database that was not explicitly tied to the Windows Group login or Windows Login-based Login was not accessible at all.

So, I'm sure something has changed beyond what was mentioned, or some other configuration creates this situation. But first, we probably should clearly understand the exact question. The description of the problem reads:

From now on, myUser can access SomeAppDb as expected, but could not access PublicDb

This should mean that myUser was able to log in. However, the user was somehow unable to switch to PublicDb via USE [PublicDb] ? Or are we talking about something else? Maybe something else that is implied in the error message:

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

If myUser was registered and simply changed the databases or fulfilled the cross-database query, then there would be no “with login error” error message. This leads me to suspect that the default database (or the database specified in the connection string) SomeAppDb and, as stated, there is no problem there. But then it should be a connection string defining "PublicDb" that had the problem. If so, will the same connection string be copied and pasted (not reprinted) for someone else? Maybe there is a type, even a hidden character, in the connection string that indicates "PublicDb"? The only way I was able to reproduce this error is to connect via SQLCMD when specifying a database that:

• does not exist
• the account had access, but with square brackets around the name (for example, -d "[PublicDb]" )
• exists, but the account does not have access to (this means there are more things to check as follows :-)

If there is no problem with the connection string, follow these steps:

• While DOMAIN\myUser registered with 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 this Login is indeed in this group, it will return 1 . If this is not the case, then either:

  • 0 means Login is a Windows Login, but not in this group, so it has been removed from the group or
  • NULL means this is not a Windows login (it seems unlikely since the SQL Server login name has \ , which is not a valid character to log into SQL Server)

• While DOMAIN\myUser written to SQL Server:

  • Remove this interim fix from user DOMAIN\myUser located in PublicDb .

  • DOMAIN\myUser should do the following:

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

    Does PublicDb 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 indeed a database. Maybe the value was set incorrect when you initially added Login for DOMAIN\myUser . If nothing else, try setting it to [master] to see if the error escalated. If this works, set it to [PublicDb] .

• Have "myUser" on Windows, go to the command line and run:

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

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

  • If so, then this is definitely not a "db access" issue.
  • If not, then this login is no longer included in this group, or something specific blocks it. In this case, something was done when adding a specific entry for DOMAIN\myUser beyond "The user was created in the SomeAppDb database with a mapping between 2." for example, permission denied at the server level or at the database level?

• Have "myUser" on Windows, go to the command line and run:

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

  They should get the returned string PublicDb DOMAIN\myUser . If so, then this is definitely not a "login" issue.

• Easy test:
  • Create New Active Directory Account
  • Make this account a member of the DOMAIN \ SomeGroup
  • Log in to Windows as this account
  • Connect to SQL Server
  • Try to access both databases. This account should only have access to PublicDb
  • Disconnect from SQL Server
  • Create an SQL Server account for this Windows Windows account
  • Create a user (without additional parameters) in SomeAppDb for this login (for example, CREATE USER [DOMAIN\myUser] FOR LOGIN [DOMAIN\myUser] )
  • Connect to SQL Server
  • Try to access both databases. This account should now be able to.
• The last thing to check is to remove all parts that were apparently the cause of this error. So get rid of the user DOMAIN\myUser in SomeAppDb , and then get rid of the DOMAIN\myUser . If one of these reasons really caused this error, then at that moment DOMAIN\myUser should again have access to PublicDb .
• Other Windows groups - is it a Windows login (at least someone with a login)?
• What "membership in the role" were you talking about? Are these built-in roles or custom roles?

---

PS Most of the related messages floating around that deal with the same error end up either creating a SQL Server login to use, or adding a Windows login to the db_owner role. I think that both of these solutions are errors and that computers are not arbitrary in their behavior, so we just need to find the reason.