sp_change_users_login (Transact-SQL)
Applies to: 
SQL Server
Maps an existing database user to a SQL Server login.
Important
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.
Transact-SQL syntax conventions
Syntax
sp_change_users_login
[ @Action = ] 'Action'
[ , [ @UserNamePattern = ] N'UserNamePattern' ]
[ , [ @LoginName = ] N'LoginName' ]
[ , [ @Password = ] N'Password' ]
[ ; ]
Arguments
[ @Action = ] 'Action'
Describes the action for the stored procedure to perform. @Action is varchar(10), with no default, and can have one of the following values.
| Value | Description |
|---|---|
Auto_Fix |
Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name doesn't exist, one is created. Examine the result from the Auto_Fix statement, to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.When you use Auto_Fix, you must specify @UserNamePattern and @Password if the login doesn't already exist, otherwise you must specify @UserNamePattern but @Password is ignored. @LoginName must be NULL. @UserNamePattern must be a valid user in the current database. The login can't have another user mapped to it. |
Report |
Lists the users and corresponding security identifiers (SID) in the current database that aren't linked to any login. @UserNamePattern, @LoginName, and @Password must be NULL or not specified.To replace the report option with a query using the system tables, compare the entries in sys.server_prinicpals with the entries in sys.database_principals. |
Update_One |
Links the specified @UserNamePattern in the current database to an existing SQL Server @LoginName. @UserNamePattern and @LoginName must be specified. @Password must be NULL or not specified. |
[ @UserNamePattern = ] N'UserNamePattern'
The name of a user in the current database. @UserNamePattern is sysname, with a default of NULL.
[ @LoginName = ] N'LoginName'
The name of a SQL Server login. @LoginName is sysname, with a default of NULL.
[ @Password = ] N'Password'
The password assigned to a new SQL Server login that is created by specifying Auto_Fix. @Password is sysname, and can't be NULL. If a matching login already exists, the user and login are mapped and @Password is ignored. If a matching login doesn't exist, sp_change_users_login creates a new SQL Server login and assigns @Password as the password for the new login.
Important
Always use a strong password.
Return code values
0 (success) or 1 (failure).
Result set
| Column name | Data type | Description |
|---|---|---|
UserName |
sysname | Database user name. |
UserSID |
varbinary(85) | User's security identifier. |
Remarks
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user changes, use sp_change_users_login to link the user to the new login without losing user permissions. The new @LoginName can't be sa, and the @UserNamePattern can't be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login can't be used to map database users to Windows-level principals, certificates, or asymmetric keys.
sp_change_users_login can't be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.
sp_change_users_login can't be executed within a user-defined transaction.
Permissions
Requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
Examples
The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A. Show a report of the current user to login mappings
The following example produces a report of the users in the current database and their security identifiers (SIDs).
EXEC sp_change_users_login 'Report';
B. Map a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped to login MaryB.
--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks2022;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
C. Automatically map a user to a login, and create a new login if necessary
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that's the password B3r12-3x$098f6 if the login Mary doesn't exist.
USE AdventureWorks2022;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO