The title reflects what I believe is the problem based on my research up to this point. Nonetheless, I will include back information in case it's relevant.
SQL Server 2008 R2 Windows Server 2008 R2 Standard
Windows 7 Professional
I have a .NET 2.0 program (herein referred to as "program") I created that, given a server and database name as parameters, automatically creates AD groups, corresponding SQL Server logins, and associated database users. This lets us manage permissions in AD and makes it so that our multiple administrators can all use the same repeatable process to set permissions. The program...
- creates a read-only and a read-write AD group for the server/database combination (I mean the names of the group have "ReadOnly" or "ReadWrite" in them -- the groups themselves have no special AD permissions)
- then, creates a login on the server named in the server parameter that corresponds to the AD group
- then, finally, creates a user in the database named in the database parameter
I set it to print the statements it executes, and they end up like this:
CREATE LOGIN [Domain\server.database.ReadOnly] FROM WINDOWS WITH DEFAULT_DATABASE=[database] CREATE USER [server.database.ReadOnly] FOR LOGIN [Domain\server.database.Readonly] EXEC sp_addrolemember N'db_datareader', N'server.database.ReadOnly'
(the naming scheme I use for these group names is "server.database.ReadOnly" or "server.database.ReadWrite" . The periods are contained within brackets when used in SQL and so should not be a problem.)
Until now, this program has been successfully run many times and set up this structure on multiple servers for multiple groups. However, recently, after I ran it for a particular couple of databases (on servers I had already used it for previously), everything was set up, but the permissions didn't take effect and the users still had no access to the databases. This was evidenced by the fact that a user who was a member of the corresponding server.database.ReadOnly group could not even connect to the database (i.e. "USE ") or expand the database node in SSMS.
Examining the differences in properties between working and nonworking groups/logins/users yielded no noticeable differences, and using SSMS "script as drop and create" on the logins/users showed no differences either.
Oddly, it seemed that running the program (an executable) on the SQL server machine housing the database itself now produced a problematic structure, while running it locally on my workstation produced a working structure. Creating the groups manually by running the commands in SSMS also produced a working structure. If you're wondering why I would run it on the server directly, it's because this would guarantee that the AD groups it created would be created on the same domain controller that SQL Server would reference when creating the logins (since replication takes time and these steps are all performed very quickly, the AD newly-created groups needed to be available for SQL Server to find immediately).
In any case, I banged my head against this for some time trying to figure out how there could possibly be any difference, and just recently, came across what I think is the most important clue:
When I connect to the server as an affected user and run
select * from sys.login_token t order by name
I can see all the AD groups the affected user is a member of. More importantly, in the principal_id column, there is a 0 for the groups (generated by my automation program) whose permissions are not taking effect, and there is a 0 for groups with no corresponding SQL Server login, while there is an appropriate nonzero number for the groups (again, generated by my automation program) whose permissions are working correctly. This leads me to believe that SQL Server is somehow unable to connect the AD groups the affected user is a member of with their SQL Server logins. The names of the AD group and login are identical as required, and the server collation is case insensitive, so that shouldn't be a problem. The working groups have names like "DomainName\server.Database.ReadOnly" while the nonworking groups have names like "DOMAINNAME\server.database.readonly", in direct contrast with the actual AD group name, which has letters capitalized, but I assume this is just more evidence that it's not making the connection. My main issue is that I can't figure out WHY it's not making the connection.
I've checked many times, and there is definitely a login with name "DomainName\server.Database.ReadOnly" with the "Windows Authentication" radio button ticked and whose default database is, appropriately, "Database". The login is mapped to user "server.DatabaseName.ReadOnly" and that user shows as being a member of roles db_datareader and public. I performed these checks using the GUI of SSMS. The affected user has logged on and off many times. I've used "Run As" to run SSMS as the affected user on my workstation with the same permission problems. I've also remoted (as the affected user) to the server in question as well as a different machine, yet continued to experience the permission issues. The affected user is a test user I created to troubleshoot this, but there is also another user who is a member of these same AD groups and has these same permission problems; that user is where the problem presented originally.
I am a sysadmin on all servers, and a domain admin. The program is run under my credentials. I use Windows Authentication (a.k.a. "Integrated"/"SSPI"/"Trusted Connection").
The closest even remotely relevant explanation I've found so far is someone mentioning cached names and case sensitivity:
Having spent so many hours banging my head against this, I'm now essentially out of things to try. Why could be the cause of these strange discrepancies?