In today’s world, mergers and acquisitions of organizations are a reality. Having been through a couple of M&As in the last couple of years myself, I have had a unique opportunity to experience changes which would not be experienced in the normal course of work.
One such change is the changing of the domain logins. M&As often come with renaming of domain logins to confirm to a common standard. The question that came up in our team was:
What happens to our SQL Servers that use windows authentication if the login name changes?
The answer? Nothing. SQL Server continues to work as usual if the login name is the only thing that changed (and your applications do not use the physical “login name” in any way).
Here’s a demo for clarity.
On my VM, I created a user “OldUser” and logged in to SSMS using Windows Authentication when running as that user.
For the record, we will also execute the following query:
SELECT SUSER_SNAME() AS LoginName,SUSER_SID() AS LoginSID;
Now, because this is a local login, all I do is rename the Windows login using Computer Management. The same behaviour applies to logins renamed via Active Directory as well.
I load the SSMS again – this time as “NewUser”.
Notice how the authentication works even though the login name has changed.
Wrapping it up…
Windows authentication in SQL Server is based on the SID. Hence, even though the login name changed, SQL Server allowed me to login because the SID did not change. In fact, this is why when we create or modify a login, the only requirement is to ensure that the SID of the login is same as the one we want to create/modify.
In a practical scenario, the IT teams would not re-generate the SIDs because that would mean too much work – realigning file & folder shares, resetting SSO and what have you. Instead, they simply update the login name (or the human interpretative part of the login).
What this means is that the authentication on the SQL Servers continue to work as usual – even after the login changes. No manual intervention is required!
Note of caution: Because the SQL Login name and the Windows login name are independent, renaming the Windows login will NOT rename the SQL login. The entry under sys.server_principals continues to read the old value. So, if your applications are designed to work based on the login name in sys.server_principals, you need to use ALTER LOGIN statements whenever IT changes the Windows login names.
In case you are really interested in digging deeper into SQL Server,
- Security considerations for a SQL Server installation [MSDN Link]
Until we meet next time,
Be courteous. Drive responsibly.
SID will not change for just a rename, but SIDs CAN change. At current client, we have had this problem when network admin moved a group of users from one OU to another. SID did change, although account name and logon name did not.
LikeLiked by 1 person
Thank-you for taking the time to read the post and sharing your feedback! Yes, in this case, the SID will change and that would have an impact on the SQL logins.
The ideal course of action is for DBAs to work with IT teams closely to understand the nature of the change and plan accordingly. In my case, the IT teams did tell me that they are simply renaming the users and that the SID would not change because they wanted to preserve all customizations, permissions, etc.
Thanks again for your time! Have a great rest-of-the-day!