2

I have a user who was added with windows authentication to a SQL Server 2008 R2 machine.

He had left the company a few months ago, at which point his AD user was disabled and moved to a different OU.
The users database logins and users were not affected.
He then returned to the company, his AD user was reactivated, and moved to the correct OU.

However all his database logins are currently giving login failures.
The issue is resolved if the following actions are taken:

  1. Drop the existing login
  2. Create the login again
  3. Remap the login to the existing database users

This is an impractical method, as it means tracking down each login and recreating it.

How can I resolve this in a more practical way?
Failing a more practical solution, what is the cause for this issue?

  • What is the error when logging in? – dwjv Oct 5 '15 at 10:33
  • @dwjv The standard error: Login failed for user:. For all intents and purposes it is as if the login does not exist. However in every case the login is present, and dropping it and recreating it fixes the problem. – Reaces Oct 5 '15 at 10:37
  • Did the sid of the domain user change? – dwjv Oct 5 '15 at 10:51
  • @dwjv I don't know how to check, but I researched the actions taken to archive the user, and it shouldn't have. (The user was disabled, and then moved to an archive OU) – Reaces Oct 5 '15 at 10:52
  • Check the SIDs of the accounts in SQL and AD before the SQL login deletion to confirm they are indeed the same and the AD admins didn't really delete and recreate the AD user account just telling you otherwise. Articles: sqlserver-help.com/2014/05/27/… and technet.microsoft.com/en-us/library/ff730940.aspx There may be a way to get both from PowerShell but confirm what the AD domain and forest functional levels are and research with your version of SQL Server for known bugs -- maybe it's time to upgrade/update one or the other. – Pimp Juice IT Oct 5 '15 at 22:30
1

Check the SIDs of the accounts in SQL and AD before the SQL login deletion to confirm they are indeed the same and the AD admins didn't really delete and recreate the AD user account just telling you otherwise.

Articles: http://sqlserver-help.com/2014/05/27/sql-internals-how-to-map-login-sid-to-domain-account/ and https://technet.microsoft.com/en-us/library/ff730940.aspx

There may be a way to get both from PowerShell but confirm what the AD domain and forest functional levels are and research with your version of SQL Server for known bugs -- maybe it's time to upgrade/update one or the other.

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.