Mapping a database user to a new SQL Server 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.


Applies to: SQL Server (SQL Server 2008 through current version).
 

Transact-SQL Syntax Conventions



Syntax

--------------------------------------------------------------------------------

Copy


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


Arguments

--------------------------------------------------------------------------------



Return Code Values

--------------------------------------------------------------------------------

0 (success) or 1 (failure)


Result Sets

--------------------------------------------------------------------------------


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 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 user cannot 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.



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

--------------------------------------------------------------------------------



A. Showing 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).
 

Copy


EXEC sp_change_users_login 'Report';
 
B. Mapping 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.
 





Copy


--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
 
C. Automatically mapping a user to a login, creating a new login if it is required


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.
 


Copy


USE AdventureWorks2012;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO
 


See Also

--------------------------------------------------------------------------------


Reference

Security Stored Procedures (Transact-SQL)

CREATE LOGIN (Transact-SQL)

sp_adduser (Transact-SQL)

sp_helplogins (Transact-SQL)

System Stored Procedures (Transact-SQL)

sys.database_principals (Transact-SQL)