If you’ve tried using Universal Groups in Active Directory to access your SQL Servers, you may notice that the users who are members of these groups can’t access the SQL Server Instance. The reason for this has more to do with active directory than with SQL Server. Normal groups in Active Directory are cached so authentication requests can return groups that the user is a member of as part of the Windows Token. Universal groups, however, aren’t included in the Windows Token as the Universal groups that the user might be a member of might not be in the same domain that the request is handled by.
The internals of why Universal Groups don’t work requires a decent understanding of the internals of Windows Authentication Tokens and Windows Security. But needless to say, all that you need to know is that Universal Groups don’t work with SQL Server.
Because the Universal Groups aren’t in the authentication token when the SQL Server goes to see if the user has access, the token says that the user doesn’t. The fix for this is quite easy, use a different Windows Domain Group type than Universal Groups.
The post Why Don’t Universal Groups Work in SQL Server? appeared first on SQL Server with Mr. Denny.