How to find orphaned users in a database. If orphan users exist on a database how to create a login by auto fixing the logins in the instance.

Problem: How to find orphaned users in a database. If orphan users exist on a database how to create a login by auto fixing the logins in the instance.

Solution: sp_change_users_login

Maps an existing database user to a SQL Server login. 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.

Syntax:

sp_change_users_login [ @Action = ] 'action'   -- AUTO_FIX|REPORT|UPDATE_ONE

    [ , [ @UserNamePattern = ] 'user' ]  

    [ , [ @LoginName = ] 'login' ]  

    [ , [ @Password = ] 'password' ] 

[;] 

Orphaned Users: When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."

  

Example: In this example, I would like to show we can find an orphaned user in a database and fix the database user by creating a login that matches the name.

Step 1: Finding orphaned users in the database.

USE DB

EXEC sp_change_users_login @Action='Report';

OUTPUT:

UserName	UserSID
app	0x6A279D3B22D6654583FC774980D493F0

Step 2: Fix the user by specifying auto_fix in the action type.

EXEC sp_change_users_login @action='Auto_Fix', @usernamepattern='app',@loginname=NULL,@password='P@$$w0rd'

OUTPUT:

The row for user 'app' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.