Del via

Fixing orphaned Windows user logins in SQL Server

  • Artikel

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.

Comments

  • Anonymous
    December 28, 2015
    Great I have tried but I have one doubt after mapping the user when we go to database property and user mapping it will show old user name with default schema . its okay ? or is their any resolution

  • Anonymous
    December 31, 2015
    Thanks Sandeep. Yeah, the username would remain the same if you use the command. The login name can change if you try to map it with a different login. The command will ensure that the SID for user and login are matching.