Solutions to isolated User Permissions problems when moving SQL Server databases

Symptom

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 northwind To disk = 'C:/MSSQL/backup/northwind. Bak'
4.	Restore the database to another SQL Server: Restore database northwind From 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.	To detect isolated users, runCode: 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 an isolated user in the previous step: Use northwind go sp_change_users_login 'Update _ one ', 'test', 'test' , connect the logon "test" on the server to the User "test" on the northwind database. The sp_change_users_login stored procedure can also use the "auto_fix" parameter to update all isolated users. However, 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 you run the code in the previous step, you can access the database. Then, you can use the sp_password stored procedure to change the password: use master go sp_password null, 'OK ', 'test' This stored procedure cannot be used for a Microsoft Windows NT Security Account. 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.