USE MASTER;
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = 'usrUser01')
BEGIN;
CREATE LOGIN usrUser01
WITH PASSWORD = 'password01',
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF
END;
GO
USE Database01;
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'usrUser01')
DROP USER usrUser01;
GO
CREATE USER usrUser01 FOR LOGIN usrUser01 WITH DEFAULT_SCHEMA=[dbo];
GO
EXEC sp_change_users_login 'Update_One', 'usrUser01', 'usrUser01';
GO
--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 sp_change_users_login
Maps an existing database user to a SQL Server login.
Syntax
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]
[;]
Arguments
[ @Action= ] ‘action‘
Describes the action to be performed by the procedure. action is varchar(10). action can have one of the following values.
| Value | Description |
|---|---|
| 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 userbut 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.
Sources:
Comments