Isolate database users

 

An isolated account means that the account of a database only has a user name but no logon name. Such a user exists in the sysusers system table of the user database, but there is no corresponding record in syslogins of the master database.

 

Two types of isolated accounts are generally generated:

1. Restore the backed up database on another machine;
2. After the system or SQL Server is reinstalled, only the user library is restored.

 

The solution is to use sp_change_users_login.

 

Sp_change_users_login can be used in three ways:

 

Usage 1:
Exec sp_change_users_login 'report'

List isolated users of the current database

 

Usage 2:
Exec sp_change_users_login 'Auto _ fix', 'username'

You can automatically add the login with the same name as the user name to syslogins.

 

Usage 3:
Exec sp_change_users_login 'Update _ one', 'username', 'username'
Maps the user name to the specified Login Name.

After the database is restored last time, because the database has an isolated user XXX after recovery, to solve the same isolated user login name problem, first create a login name with the login name xxx, then, exec sp_change_users_login 'Auto _ fix' is called. After 'xxx' is executed, the Association is resolved.