I was trying to grant a developer read-only access to a database. (I know!) I created an Active Directory domain-level group, added his account to the group, created a login for the group, and made it a member of the db_datareader role in the database. But he got this error when he tried to select from a table (using Windows authentication):
He tried closing and re-opening SQL Management Studio but that didn't help. He had to log out of Windows and back in. This suggests your account queries AD when you log in to Windows and passes cached details to SQL Server. But I'm just guessing.
Can you point me at something I can read to understand this interaction, please?
Kev is right.
When you authenticate you are granted an access token that identifies you by your domain SID, your group memberships (their SIDs) and your privileges and rights to resources. As a security measure these tokens cannot be altered without re-authentication.
The new group that was created was identified by a SID that was not included in the access token until they re-authenticated. As a result underlying ACLs could not match the SID they contained with any providing access from the user's access token.
As you said David - a puzzler - and one that catches a lot of people, a lot of times.
answered Feb 23 '12 at 01:25 PM
When a user logs on, it collects all the SIDs (security identifiers) including the SIDs of the security groups it is member of. When accessing resources on the network, the SIDs get evaluated against the SIDs that are configured on the resources. So whenever a group membership changes, the user has to logoff/logon to get the new SIDs. Hope it helps.
answered Feb 23 '12 at 01:15 PM