How to map an existing database user to a SQL Server login

[Niranjan]

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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

Management Studio template

EXECUTE [master].[dbo].[sp_change_users_login]
@Action = '<Action,varchar(10),Update_One>',
@UserNamePattern = N'<UserName,sysname,>',
@LoginName = N'<LoginName,sysname,>',
@Password = NULL;