Change SQL Server to change the owner of the current database: Sp_changedbowner_mssql

Sp_change_users_login

Changes the relationship between SQL Server users and Microsoft®sql Server™ logins in the current database.

Grammar

sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @login Name = ] 'login' ]

Parameters

[ @Action = ] ' Action '

Describes the action to be performed by this procedure. The action 's data type is varchar, which can be one of the following values.

value	Description
Auto_Fix	Link the user entry in the sysusers table of the current database to a login with the same name in syslogins . It is recommended that you check the results of the Auto_Fix statement to confirm that the link you established is the expected result. Avoid the use of auto_fixin situations where security is more sensitive. Auto_Fix makes the best estimate of the link, giving the user more access than expected. user must be a valid user in the current database,login must be NULL, a zero-length string ('), or unspecified.
The	Lists the users in the current database that are not linked to any logins and their corresponding security identification numbers (SIDS). user and login must be NULL, a zero-length string ('), or unspecified.
Update_One	Links the user specified in the current database to login. Login must already exist. user and loginmust be specified.

[@UserNamePattern =] ' User '

is the name of the SQL Server user in the current database. The user 's data type is sysnameand the default value is NULL. sp_change_users_login can only be used for SQL Server logins and user security accounts, not for Microsoft Windows NT® users.

[@LoginName =] ' Login '

The name of the SQL Server login. The data type of login is sysnameand the default value is NULL.

Return code value

0 (Success) or 1 (failed)

Result set

Column Name	Data Type	Description
UserName	sysname	The login name.
UserSID	varbinary (85)	Login security identifier.

Comments

Use this procedure to link a security account for a user in the current database to a different login. If a user's login has changed, the user is linked to a new login using sp_change_users_login without losing the user's permissions.

login cannot be sa, and user cannot be dbo,guest , or information_schema users.

sp_change_users_logincannot be executed in a user-defined transaction.

Permissions

Members of any public role can perform the sp_change_users_loginwith the "with" options. Only members of the sysadmin fixed server role can specify the auto_fix option. Only members of the sysadmin or db_owner role can specify the update_one option.

Example

A. Displaying the current user's report for a login mapping

The following example generates a report of users and their security identifiers in the current database.

EXEC sp_change_users_login 'Report'

B. Changing the user's login

The following example changes the link between the user Mary in the pubs database and the existing login and links to the new login newmary (added using 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'