Updated: December 13, 2016
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.
sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ] [ , [ @LoginName = ] 'login' ] [ , [ @Password = ] 'password' ] [;]
[ @Action= ] 'action'
Describes the action to be performed by the procedure. action is varchar(10). action can have one of the following values.
|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 does not exist, one will be 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 user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
|Report||Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, 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 user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not specified.|
[ @UserNamePattern= ] 'user'
Is the name of a user in the current database. user is sysname, with a default of NULL.
[ @LoginName= ] 'login'
Is the name of a SQL Server login. login is sysname, with a default of NULL.
[ @Password= ] 'password'
Is the password assigned to a new SQL Server login that is created by specifying Auto_Fix. If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login. password is sysname, and must not be NULL.
IMPORTANT!! Always use a strong Password!
0 (success) or 1 (failure)
|Column name||Data type||Description|
|UserName||sysname||Database user name.|
|UserSID||varbinary(85)||User's security identifier.|
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 has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the usercannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
sp_change_users_login cannot 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 cannot be executed within a user-defined transaction.
Requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
The following example produces a report of the users in the current database and their security identifiers (SIDs).
EXEC sp_change_users_login 'Report';
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
--Create the new login. CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3'; GO --Map database user MB-Sales to login MaryB. USE AdventureWorks2012; GO EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB'; GO
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 has the password
B3r12-3x$098f6 if the login
Mary does not exist.
USE AdventureWorks2012; GO EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6'; GO