SQL Server account isolate problem resolution

The site has often been attacked, several times the database hanging horse, a few days before the forum upgraded, and today upgraded the database, the former MSSQL 2000 upgrade to MSSQL 2005, with the database restore function to restore, encountered the problem of isolated account.

The site has often been attacked, several times the database hanging horse, the forum a few days ago upgraded, and today upgraded the database, put before the MS SQL2000 Upgrade to MS SQL2005, with the database restore function restored, encountered the problem of isolated account.

What is a problem with orphaned users?

For example, many of the tables in the previous database were created by user test, but when we recovered the database, the test user became an orphaned user with no login username, even though you created a test login username and a previous user password. The user who has previously belonged to test is also not able to manipulate the user's table after logging in.

Google a bit, there are a lot of solutions, but a lot of solutions are not implemented, because of the rights of MsSQL 2005 relationship. Here's a solution I've validated:

After we have created the same name of the database login user, the table in the database we can not use the same because the Sid is different, is the system login table and database user table in the same user name, only Sid field, the database or the previous old system SID value, So we're going to match it to our new database, which relies on SIDS to identify users.

Stored procedure sp_change_users_login can be used here. It has three kinds of movements, namely Report,update_one and Auto_Fix. Running the following code, the system lists the number of orphaned users for the current database. Sp_change_users_login ' We only need to select the current database as TestDB and then run

Sp_change_users_login ' Update_One ', ' Test ', ' test '

The system prompts you to fix an orphaned user. If you do not have a logon user to create test, you can also use the

Sp_change_users_login ' Auto_Fix ', ' Test ', NULL, ' Testpassword '

To create a login user named Test, which corresponds to the user with the password Testpassword. Typically, the problem with database objects being accessed is resolved. If there are multiple databases with the same user's datasheet, just select a different database and execute the Update_One.