Change SQL Server to change the current database owner: sp_changedbowner

Sp_change_users_login

Change the SQL Server user and Microsoft®SQL Server™The relationship between logon.

Syntax

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @loginName = ] '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®User.

[@ 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'