Reestablishes foreign key relationships from the syslogins to sysusers and sysalternates tables in cases where cross-server DUMP or LOAD database activity has broken these relationships. Without reestablishing relationships, orphan users may exist.
sp_change_users_login {Auto_Fix | Report | Update_One}
[, 'UserNamePattern' [, 'LoginName']]
where
| Action | Description |
|---|---|
| Auto_Fix | Links existing orphan usernames to logins of the same name. If an alternate username exists, each entry in the sysusers table is linked with an entry in the syslogins and sysalternates table. It is recommended that you monitor the outcome from the Auto_Fix statement. When linking rows, the Auto_Fix option makes assumptions that may be incorrect. |
| Report | Lists all usernames in sysusers that have no link to any login name in syslogins. This is the default. |
| Update_One | Links a specified orphan username in sysusers to an existing login name in syslogins. An orphan username has an entry in the sysusers table that does not have a matching entry in the syslogins table. |
Important Avoid using Auto_Fix in security-sensitive situations. The procedure makes best estimates on links, which could allow a user more access privileges than intended.
| UserNamePattern must be: | Action |
|---|---|
| Any non-NULL user-name pattern that is compatible with the LIKE operator. | Auto_Fix |
| NULL (the default). | Report |
| One username. | Update_One |
It is suggested that you use this stored procedure after running the LOAD DATABASE statement because sp_change_users_login eliminates the need for manual updates for aliases or alternate usernames.
This stored procedure corrects a potential violation of referential integrity between syslogins and sysusers after DUMP and/or LOAD DATABASE activity. If the LOAD DATABASE statement runs and entries in the sysusers table do not have matching entries in the syslogins table, the tables have missing links. Missing links can prevent some users from using the database.
Missing links are created when login or user modifications occur between the DUMP and LOAD statements or if the dump and load are performed on different servers. For example, if a database is loaded from a database backup from six months ago that had only 100 users and the current version of the database has 130 users, the most recently added 30 users may not be able to access the database loaded from the backup. Reestablishing links by using sp_change_users_login can synchronize the entries in syslogins to the corresponding entries in sysusers and sysalternates, where aliases are used.
This procedure can also relink entries in the syslogins table to entries in the sysalternates table by using the Auto_Fix option.
Users such as guest, public, sa, probe, repl_publisher, repl_subscriber, and any group are not processed by this procedure. User logins that meet the conditions specified in UserNameorPattern (Auto_Fix) or LoginName (Update_One) will be processed with this procedure.
If this procedure completes normally, 0 is returned. If this procedure raises an error, 1 is returned.
Only the system administrator can run Auto_Fix. The public group can run Report. Only the system administrator or the database owner can run Update_One.