12

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?

  • 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
  • Spot checking various Domain Controllers I see that replication is indeed finished after 15 minutes, but SQL ignores the new user to the AD group. Restarting SQL server resolves the issue, so does waiting 24 hours. Must be a better way. – Aaron Auseth Jan 4 '17 at 23:15
12

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.

  • 1
    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. – NateJ May 5 '16 at 19:19
  • @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. – SomeGuy Jul 13 '17 at 18:56
  • For service accounts, all you can do it restart the service on the machine which needs the new rights. – mrdenny Dec 31 '17 at 20:29
5

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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