By D_Bester


2013-02-26 06:30:22 8 Comments

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?

3 comments

@Karl 2017-02-21 18:12:53

So you get immediate results would be to get new credentials every time by running a cmd/script like:

runas /netonly /user:domain\username "sqlcmd -S serverName -d dbname -q \"insert into testpermissions values (65)\""

using cmd.exe (not powershell, i couldn't get the quoting correct).

That way you will get a new token each and everytime (but have to enter your pwd). You could probably do something with a saved password text too, if things were too onerous.

Anyways, works for me and I hope it helps somebody else.

@mrdenny 2013-04-28 09:23:30

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.

@NateJ 2016-05-05 19:19:47

Is there a command or script one can run on the workstation to "refresh" its AD token so that it can "know" the user is now a member of the new domain-group, and thus avoid the delay? --Update: seems that one person recommended klist purge at dba.stackexchange.com/a/44922/29371, but with the caveat that it might break other cached resource accessors which the user would have to re-establish/re-connect-to.

@SomeGuy 2017-07-13 18:56:52

@mrdenny Any recommendations on how to handle this for service accounts? We run all SQL instances under service accounts, and use these service accounts for connections between SQL Servers, and for SSRS, etc. At any given time, we have many connections between machines. We can't really disconnect all connections and do a fresh login somewhere to get new AD group info.

@mrdenny 2017-12-31 20:29:50

For service accounts, all you can do it restart the service on the machine which needs the new rights.

@Jon Seigel 2013-02-26 14:28:18

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.

Related Questions

Sponsored Content

1 Answered Questions

2 Answered Questions

2 Answered Questions

2 Answered Questions

4 Answered Questions

1 Answered Questions

1 Answered Questions

Sponsored Content