Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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).
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.
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).
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.
--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.
USE AdventureWorks2012; GO EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6'; GO