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?

Improve this question
6
  • What is the error when logging in?
    – dwjv
    Oct 5, 2015 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, 2015 at 10:37
  • Did the sid of the domain user change?
    – dwjv
    Oct 5, 2015 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, 2015 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.
    – IT Thug Ninja
    Oct 5, 2015 at 22:30

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

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