13

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?

Improve this question
3
  • 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
    Commented Feb 26, 2013 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
    Commented Feb 26, 2013 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
    Commented Jan 4, 2017 at 23:15

3 Answers 3

Reset to default
14

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.

Improve this answer
3
  • 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
    Commented May 5, 2016 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
    Commented Jul 13, 2017 at 18:56
  • For service accounts, all you can do it restart the service on the machine which needs the new rights.
    – mrdenny
    Commented Dec 31, 2017 at 20:29
6

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.

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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