Permissions puzzler

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): Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'tablename', database 'databasename', schema 'dbo'.

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?

asked Feb 23 '12 at 12:34 PM in Default

David Wimbush
4.2k ● 25 ● 29 ● 31

You've probably checked this one already, but has he re-authenticated, i.e. logged off the domain and back on again.

Feb 23 '12 at 12:38 PM Kev Riley ♦♦

Sorry Kev, he came back with an update so I was editing the question when you commented. Yes, logging out and back in worked. Do you know how this works, please?

Feb 23 '12 at 12:44 PM David Wimbush

Hmm in exact detail no. Essentially when you log on to the domain, you get tokens that correspond to your set of permissions (for all kinds of resources : shares, files, folders, services, etc.etc.). Some of those don't get refreshed until you force the re-authentication.

Feb 23 '12 at 12:51 PM Kev Riley ♦♦

(comments are locked)

2 answers:

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

Blackhawk-17
10.5k ● 23 ● 29 ● 34

That makes sense now. Thanks, Greg.

Feb 23 '12 at 01:49 PM David Wimbush

(comments are locked)

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