Document directory
Sp_change_users_login
Change the relationship between SQL Server users and Microsoft SQL Server logon in the current database.
Syntax
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @login
Name = ] 'login' ]
Parameters
[@ Action =]'Action'
Describes the operations to be performed in this process.ActionThe data type of isVarchar (10), Which can be a value below.
Value |
Description |
Auto_Fix |
SysusersLink user entries in the tableSyslogins. Recommended checkAuto_FixStatement, and confirm that the established link is the expected result. Avoid usingAuto_Fix.Auto_FixMake the best estimation on the link, and grant the user more access permissions than expected. UserMust be a valid user in the current database,LoginIt must be NULL, a zero-length string (''), or not specified. |
Report |
List the users that are not linked to any logon in the current database and their corresponding Security ID (SID ). UserAndLoginIt must be NULL, a zero-length string (''), or not specified. |
Update_One |
UserLinkLogin.LoginMust already exist. Must be specifiedUserAndLogin. |
[@ UserNamePattern =]'User'
Is the name of the SQL Server user in the current database.UserThe data type of isSysnameThe default value is NULL.Sp_change_users_loginIt can only be used for SQL Server logon and user security accounts; it cannot be used for Microsoft Windows NT users.
[@ LoginName =]'Login'
The name of the SQL Server logon.LoginThe data type of isSysnameThe default value is NULL.
Return code value
0 (successful) or 1 (failed)
Result set
Column name |
Data Type |
Description |
UserName |
Sysname |
Login Name. |
UserSID |
Varbinary (85) |
Logon security identifier. |
Note
Use this process to link the Security Accounts of users in the current database to different logins. UseSp_change_users_loginLink a user to a new logon without losing the user's permissions.
LoginCannot beSa, AndUserCannot beDbo,GuestOrINFORMATION_SCHEMAUser.
Cannot be executed in user-defined transactionsSp_change_users_login.
Permission
AnyPublicAll members of a role can executeReportOptionalSp_change_users_login. OnlySysadminOnly members with fixed server roles can be specifiedAuto_Fix. OnlySysadminOrDb_ownerRole members can be specifiedUpdate_One.
Example A. display the current user report for login ing
The following example generates a report on users and their security identifiers in the current database.
EXEC sp_change_users_login 'Report'
B. Change User Logon
The following example changesPubsUsers in the databaseMaryLink to the new LogonNewMary(UseSp_addloginAdd ).
--Add the new login.USE mastergoEXEC sp_addlogin 'NewMary'go--Change the user account to link with the 'NewMary' login.USE pubsgoEXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'