#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login


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.

Screenshot showing the use of an existing windows login for authentication into a SQL Server instance.

Using an Old Login to login to SSMS

For the record, we will also execute the following query:

SELECT SUSER_SNAME() AS LoginName,SUSER_SID() AS LoginSID;
Query showing the Old User's SID

OldUser’s SID

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.

Renaming the "OldUser" to "NewUser" in Computer Management

Renaming the “OldUser” to “NewUser”

I load the SSMS again – this time as “NewUser”.

Login to SSMS using the new, renamed login

Login to SSMS using the new, renamed login

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.

Further Reading

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.

Advertisements

2 thoughts on “#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login

  1. Mark Harr

    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.

    Liked by 1 person

    Reply
    1. nakulvachhrajani Post author

      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!

      Like

      Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s