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?