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.