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?
more ▼

asked Feb 23, 2012 at 12:34 PM in Default

David Wimbush gravatar image

David Wimbush
5.1k 29 31 34

You've probably checked this one already, but has he re-authenticated, i.e. logged off the domain and back on again.
Feb 23, 2012 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, 2012 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, 2012 at 12:51 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.
more ▼

answered Feb 23, 2012 at 01:25 PM

Blackhawk-17 gravatar image

11.9k 28 31 37

That makes sense now. Thanks, Greg.
Feb 23, 2012 at 01:49 PM David Wimbush
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Feb 23, 2012 at 01:15 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 23, 2012 at 12:34 PM

Seen: 1043 times

Last Updated: Feb 23, 2012 at 01:49 PM