sp_change_users_login
Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.
Syntax
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
Arguments
[@Action =] 'action'
Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.
| Value | Description |
|---|---|
| Auto_Fix | Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.
user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified. |
| Report | Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.
user and login must be NULL, a zero-length string (''), or not specified. |
| Update_One | Links the specified user in the current database to login. login must already exist. user and login must be specified. |
[@UserNamePattern =] 'user'
Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.
[@LoginName =] 'login'
Is the name of a SQL Server login. login is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column name | Data type | Description |
|---|---|---|
| UserName | sysname | Login name. |
| UserSID | varbinary(85) | Login security identifier. |
Remarks
Use this procedure to link the security account for a user in the current database with a different login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing the user's permissions.
login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.
sp_change_users_login cannot be executed within a user-defined transaction.
Permissions
Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.
Examples
A. Show a report of the current user to login mappings
This example produces a report of the users in the current database and their security identifiers.
EXEC sp_change_users_login 'Report'
B. Change the login for a user
This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).
--Add the new login.
USE master
go
EXEC sp_addlogin 'NewMary'
go
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'