Due to some circumstances I had to dis-join from the domain Windows Server 2012 R2 based machine with SQL Server 2014 Business Intelligence Edition installed . Let's call it 'mydomain.local'. Then I totally reinstall AD DS role on domain controller machine and re-create domain 'mydomain.local' from scratch. After that SQL Server machine was joined again to the "new" 'mydomain.local' domain. Now I try to connect to Database Engine with SSMS as 'mydomain\Administrator' and receive an error:

Cannot connect to SQLSERVER.
ADDITIONAL INFORMATION: Login failed for user 'mydomain\Administrator'. (Microsoft SQL Server, Error: 18456) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

Error 18456 has several states that can be viewed in SQL Server Error Log and it's State 5 in my case. Error states explanation is given here.

I still can login onto SQL Server as local administrator. I understand that the reason of the issue is that 'new' login having the same name has different sid. And I think I should drop login 'mydomain\Administrator' and the re-add 'new' login and assign sysadmin role to it. But dropping the login is also a problem because of:

Login 'mydomain\Administrator' owns one or more database(s). Change the owner of the database(s) before dropping the login.

Of course I can change database ownership to, for example, local administrator and switch it back after re-adding login for domain user. But I'm curious are there any smarter ways to substitute 'old' domain login with a 'new' one?


There's probably a way to "hack" sys.syslogins, but I wouldn't recommend trying. It would probably be easier to just recreate the account.

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.