Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP.

I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems there is a delay before the user is recognized in Sql Server.

I am using AD Security Groups for permissions expressly so that I don't need to add individual users in Sql Server. So effectively I don't need to do anything but add the user to the AD Security Group in order to grant access.

But for some reason Sql Server doesn't immediately recognize the addition. I've seen this a number of times. I add the user to the group but that user can't access data until the next day. It seems that it doesn't query the Active Directory in real time. Can you confirm that is so?

What do I need to do so that Sql Server "refreshes" the list of users from Active Directory?

share|improve this question
1  
No repro. Group membership is immediately visible to any new authentication. Existing tokens don't change. Describe your exact steps. –  Remus Rusanu Feb 26 '13 at 6:54
    
In SQL Server 2008 Management Studio, I added a server login using the Security Group and on the database created a user mapped to that login. That works great! –  D_Bester Feb 26 '13 at 8:03
    
However after I added a new user to the Security Group it could not access the specified database. That user has access to the server using another group, so simply testing the connection to Sql Server worked fine. I was adding a connection to Sql Server for the user (on her computer). When I specified the database I wanted it said the database was unavailable. –  D_Bester Feb 26 '13 at 8:09
    
By the way: what do you mean by "No repro"? and "Existing tokens don't change"? Sorry I'm a newbie, but I see clues here that could help me understand what is going on. –  D_Bester Feb 26 '13 at 8:12
add comment

2 Answers

up vote 2 down vote accepted

The user need to log off of their workstation and log back on. That's why the changes appear to take effect the next day. The reason for this is that when the user logs on the next day they get a new token from the domain controller and this token contains the list of domain groups that they are a member of. This token with the list of domain groups is only updated when the user logs in to their computer, so if the user never logs out the token isn't ever updated.

There's also multi-site domain replication delays which need to be taken into account if your domain controllers are in different physical locations.

share|improve this answer
add comment

When a user logs on, they're assigned a security token that includes all the information about their group membership.

This token persists until the user logs off -- at which point it's discarded -- even if you make changes to the group membership in AD in the mean time. The changes you make will only take effect the next time the user logs on and receives a new security token.

You can reproduce the same scenario when assigning permissions on a file system, for example; it's an AD behaviour, not a SQL Server behaviour.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.