A few days ago at work office, we faced a problem that was a total disastrous. No one was able to log into the SQL Server instances since the network team had needed to change the domain name as well as all the login names. All the applications were running well as they were using SQL logins for authentication. The problem was that we had lost control over servers because every sysadmin login was in the old domain.

At first rebuilding the master database seemed to do the trick, but applying its backup afterwards would bring the server back to where it was. So what is a solution?

Solution

After searching on the Internet, I managed to find a clue on MSDN which implied that Local Administrators on the machine residing SQL server could log in by putting the instance in single mode. By doing that I managed to regain control over servers. Here are the detailed steps:

1.Put the instance on single mode by adding –m to the startup parameter of MSSQLSERVER service. Run SQL server configuration manager .Choose SQL server services from the left panel and bring on the SQL Server property window.

Go to the startup parameters tab . Add –m in SQL 2012 onwards and ;-m in earlier versions as illustrated bellow.

Restart the SQL server service .

Now you can open up a command prompt and type in sqlcmd to connect to the default instance or sqlcmd –S .\instancename

Type in this code. Replace domain with the name of your domain and login with the login name you use in Windows.

  CREATE LOGIN [domain\login] FROM WINDOWS  

Hit enter and then type "GO"

Now that the login has been created successfully, it needs to be assigned to sysadmin  role:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [domain\login]

Or in earlier versions:

EXEC master..sp_addsrvrolemember  N' domain\login',  N'sysadmin'

Put the server back to multi user by removing the  -m parameter that has been added earlier.

Restart SQL Server service .

Conclusion

Changing the domain name rarely happens, but as I stated above it can cause a trouble if all sysadmin logins belong to old domain name. The fact that local administrators still have rights to access the local SQL server came as a complete surprise to me since I believed as of SQL Server 2005 they did not have that permission, but when we think about it deeply ,It would be convincing.