Map existing database users to SQL Server login names

Map existing database users to the SQL Server login name.

Note:
Sp_change_users_login cannot be used as a Windows login name.

Transact-SQL syntax conventions

Syntax

sp_change_users_login [ @Action = ] 'action'     [ , [ @UserNamePattern = ] 'user' ]     [ , [ @LoginName = ] 'login' ]         [ , [ @Password = ] 'password' ]

Remarks

Note:
Sp_change_users_login cannot be used to map database users to Windows-level subjects, certificates, or asymmetric keys.

Use sp_change_users_login to link the database users in the current database to the SQL Server login name. If the user's logon name has been changed, use sp_change_users_login to link the user to a new logon without losing the user's permissions. The new Login cannot be SA, but the user cannot be DBO, guest, or information_schema.

You cannot execute sp_change_users_login in a user-defined transaction.

Parameters

[@ Action =] 'action'

Describes the operations to be performed. The data type of action is varchar (10 ). Action can have one of the following values.

Value	Description
Auto_fix	Link user entries in the sysusers table of the current database to the SQL Server login name with the same name. If there is no login name with the same name, one will be created. Check the result of the auto_fix statement and check whether the actual link is correct. Avoid using auto_fix when you are sensitive to security. If the username does not exist when auto_fix is used, the user and password must be specified. Otherwise, the user must be specified, but the password will be ignored. Login must be null. The user must be a valid user in the current database. You cannot map another user to this login name.
Report	List users that are not linked to any login name in the current database and their corresponding Security Identifiers (SID ). User, login, and password must be null or not specified.
Update_one	Link the specified user in the current database to the existing SQL Server login. User and login must be specified. Password Must be null or not specified.

[@ Usernamepattern =] 'user'

The username in the current database. The user data type is sysname, and the default value is null.

[@ Loginname =] 'login'

The name of the SQL Server logon. The data type of login is sysname. The default value is null.

[@ Password =] 'Password'

The password assigned by the new SQL Server login name created by specifying auto_fix. If a matched login name exists, the user name is mapped to the login name and the password is ignored. If no matched login name exists, sp_change_users_login creates a new SQL Server login name and assigns a password as the password for the new login name. The password data type is sysname and cannot be null.

Security note:
We recommend that you use a strong password. For more information, see .

Return code value

0 (successful) or 1 (failed)

Result set

Column name	Data Type	Description
Username	Sysname	Database username.
USERSID	Varbinary (85)	The Security Identifier of the user.

Permission

The member identity of the db_owner fixed database role is required. Only members of the SysAdmin fixed server role can specify the auto_fix option.

Example
A. display the current user report for login ing

The following example generates a report on users and their Security Identifiers (SID) in the current database.

	Copy code
EXEC sp_change_users_login 'Report';

B. map database users to the new SQL Server login name

In the following example, the database user is associated with the new SQL Server login name. Database UserMB-SalesFirst map to another login name, and then re-map to the login nameMaryB.

	Copy code
--Create the new login.CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';GO--Map database user MB-Sales to login MaryB.USE AdventureWorks;GOEXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';GO

C. Automatically map the user to the login name (Create a New login name if necessary)

The following example shows how to useAuto_FixMap existing users to login names with the same name, and how to map existing users to non-existing login namesMaryThe password isB3r12-3x$098f6SQL Server login nameMary.

	Copy code
USE AdventureWorks;GOEXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';GO

See

Secure Stored Procedure (TRANSACT-SQL)
Create login (TRANSACT-SQL)
Sp_adduser (TRANSACT-SQL)
Sp_helplogins (TRANSACT-SQL)
System Stored Procedure (TRANSACT-SQL)

Help and information