PRB: the topic "isolated user troubleshooting" in online books is incomplete.

Symptom

When you restore the database backup to another server, you may encounter isolated users. In SQL Server books onlineIsolated user troubleshootingThe specific steps to solve the problem are not described in the topic.
This article describes how to solve the problem of isolated users.

More information

Although the terms "login" and "user" are frequently exchanged, they are quite different. Logon is used for user authentication, while database user accounts are used for database access and permission verification. Logon is associated with the user through the security identifier (SID. To access the SQL Server server, you must log on. The process of verifying whether a specific logon is valid is called "authentication ". Login must be associated with the SQL Server database user. You use a user account to control activities executed in the database. If the database does not have a user account for a specific logon, the user using this logon cannot access the database even if they can connect to the SQL Server server. However, the only exception to this situation is when the database contains a "guest" user account. Logon that is not associated with the user account will be mapped to the guest user. On the contrary, if there is a database user but there is no associated login, the user will not be able to log on to the SQL server.
When the database is restored to another server, the database contains a group of users and permissions, but the corresponding logon or logon users may not be the same. This situation is called "isolated user ".

Isolated user troubleshooting

When you restore the database backup to another server, you may encounter isolated users. The following describes how to solve the problem.

1. Add a login to the primary database and specify the default database as northwind:

 
Use Master Go sp_addlogin 'test', 'Password', 'northwind'
 

2. GRANT access permissions to the created user:

Use northwind go sp_grantdbaccess 'test'
 

3. Back up the database.

 
Backup database northwindto disk = 'C: \ MSSQL \ backup \ northwind. Bak'
 

4. Restore the database to other SQL Server servers:

 
Restore database northwindfrom disk = 'C: \ MSSQL \ backup \ northwind. Bak'
 

The recovered database contains the user named "test" but does not have the corresponding logon, which leads "test" to become an isolated user.

5. Now, to detect isolated users, run thisCode:

 
Use northwind go sp_change_users_login 'report'
 

The output lists all logins, includingSysusersSystem table and master databaseSysxloginsEntries that do not match in the system table.

 

Steps for solving isolated user problems

1. Run the following command for the isolated user in the previous step:

Use northwindgosp_change_users_login 'Update _ one', 'test', 'test'
 

In this way, the serverLogin"Test" and northwind databaseUser"Test" is reconnected.Sp_change_users_loginYou can also use the auto_fix parameter to update all isolated users in the stored procedure, but this is not recommended because SQL server will try to match the login and user by name. In most cases, this is feasible. However, if a user is associated with a wrong logon, the user may have wrong permissions.

2. After running the code in the previous step, the user can access the database. Then you can useSp_passwordStored Procedure password change:

 
Use mastergosp_password null, 'OK', 'test'
 

This stored procedure cannot be used for Microsoft Windows NT Security Accounts. Users who connect to the SQL server through a Windows NT network account are authorized by Windows NT. Therefore, these users can only change their passwords in Windows NT.
Only members of the SysAdmin role can change the logon password of other users.

 

Reference

For more information about how to solve the permission problem when moving databases between servers, clickArticleNumber to view the articles in the Microsoft Knowledge Base:

240872 (http://support.microsoft.com/kb/240872/) INF: how to resolve permission issues when moving databases between SQL servers

from: http://support.microsoft.com/kb/274188/