Fixing orphaned Windows user logins in SQL Server

This is going to be a short blog post on a scenario which I encountered recently. Although there is MSDN documentation around this but I did not find anything which explicitly called out the scenario I had encountered, hence penning this down.

Most SQL DBA's are aware of the stored procedure – sp_change_users_login which is generally used to report and fix orphaned SQL SERVER LOGIN’s. I am explicitly mentioning this since the stored procedure cannot be used for Windows Logins. This has been explicitly pointed out in books online –

sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.

 

If you still want to experiment -

EXEC sp_change_users_login 'Update_One', 'TestUser', 'Testdomain\TestUser';

GO

Then you will encounter the below error –

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114

Terminating this procedure. The User name 'TestUser' is absent or invalid.

 

So what do you do when you want to fix orphaned Windows users.

In such case you can use alter user [user_name] with login = [login_name] to fix orphaned windows login. This command re-maps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

 

Additionally, while researching on this topic I also found that sp_change_users_login is deprecated. 

From books online again - This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

Hence you can now use ALTER USER command for all type of users.

 

Incase you are interested in finding out orphaned Windows Users, you can use the below query –

SELECT NAME,SID FROM SYS.DATABASE_PRINCIPALS

WHERE

TYPE = 'U' --Users only

AND AUTHENTICATION_TYPE = 3 --Windows Logins only

AND NAME NOT LIKE 'DBO'

EXCEPT

(SELECT DP.NAME, DP.SID FROM SYS.DATABASE_PRINCIPALS DP

INNER JOIN SYS.SERVER_PRINCIPALS SP ON DP.SID = SP.SID 

WHERE DP.TYPE = 'U'

AND DP.AUTHENTICATION_TYPE = 3)

 

Hope this helps you in case you are stuck with these orphaned users! Do send in your comments/thoughts.

Disclaimer: The information in this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my opinion. Inappropriate comments will be deleted at the authors discretion. All code samples are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular.