Home / server / Questions / 563944
In Process
nepdev
Asked: 2013-12-29 08:57:17 +0800 CST

SQL Server Database Access fails when User Granted Acess via Domain Group

  • 772

On a SQL Server 2008 R2 (running on Win2003), we have an issue with logins via domain groups - login access is granted to the server instance, but cannot access certain databases on the server.

The SQL server machine is a member of our AD domain, but is not a domain controller. We are using Windows Authentication on the SQL Server.

We create SQL Server logins for domain groups. We set the user mappings to a database XXX on that server, we enable the roles datareader, datawriter and ddladmin. What happens then is - the domain users that are members of those groups can connect to the SQL Server, but not to this database XXX. If user "user1" tries to connect he gets the error "Cannot open database 'XXX' requested by the login. The login failed. Login failed for user 'domain\user1'."

If I manually add 'domain\user1' to the database XXX and enable datareader, datawriter roles, then user1 has no problems to connect.

Curiously, user1 can connect to 2 other databases on that same server - also here via an AD domain group, but a different group created earlier, and the user is NOT set up individually as user on those databases. Those databases are both copied over from an older SQL Server 2005 installation via detach-attach and were working fine on SQL-2005 and also work fine now on that SQL-2008 R2 server. (They show the same user account as owner in the DB properties as the problematic database XXX.)

Database XXX is a new database created on that R2 server and the AD domain group is also new.

I can add the users manually as workaround for now, but this will not work forever, and I need a hint as what goes wrong here. Additional data - SQL Server service runs on a domain admin account.

I found an article that describes this exact same problem for SQL server 2005: https://connect.microsoft.com/SQLServer/feedback/details/248615/login-fails-when-user-is-granted-access-via-a-domain-group But that's from 2006, contains no clue as to a solution, and one should assume it has been fixed.

sql-server

2 Answers

  1. Complete aside: Running SQL Server as a domain admin is not best practice. You might want to consider running SQL as an unprivileged domain user.

    As to the users who can access databases moved via detach/attach--SIDs travel with the database.

    If I had to guess, based on the behavior, I would either say SQL is having trouble determining group membership, or that the permissions on the databases themselves are missing.

    You might want to run SELECT * FROM sys.database_principals for more information on your specific databases and make sure your Domain group is there.

    • 1

  2. If I had to guess, your users are not logging off and back on after being added to the group that you're using as e SQL login. A user's access token is not updated with new group membership until that user logs off and back on to their workstation. This would explain why older groups work but the new one that you're testing does not.

    • 0