- Rebuilding and Recovering the <tt>master</tt> Database
- Potential Problems in Rebuilding and Restoring the <tt>master</tt> Database
- Recovering Other Databases after <tt>master </tt> Has Been Rebuilt
- Notes on Logins and Users after a <tt>master</tt> Rebuild
- Program to Generate Logins from the User Database
- Remapping Orphaned Users
- Recovering msdb
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Notes on Logins and Users after a master Rebuild
If you have backed up your master database and user databases at the same time, then restoring will be easy. But if for some reason you didn't back up your master, or can't recover it from a backup, things could get tricky. Specifically, user logins for the server may be out of synchronization with the users in the database. The following discussion will walk you through this scenario.
For purposes of discussion, let's assume the following:
You do not have the latest copy of the master database.
You have added a whole lot of server logins and users to the database.
After you do a rebuild of the master database and then restore it, you may encounter problems with users trying to log in. This is because the users no longer have logins in the server, but they do still have user assignments in the user database.
What's important is that the SIDs are the same in both tables. For example, I could have the syslogins and sysusers tables match up by name, but not SID number. If this happened, users would not be able to log in. Here's an example of that:
print 'master..syslogins:'
select name, sid from master..syslogins order by name print 'mcbath..sysusers:' select name, sid from mcbath..sysusers where sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name
master..syslogins: name sid
BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01 test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x1CF91F72D5C03C49A982EBCDA3756F9B test_login_2 0xFB518D2DCD41F54088F1A613B0F0E9E1 test_login_3 0x779A044E08943544A53E1D4E604A27AD test_login_4 0x4DB074BF2B52EF40AA7982D11528C268 test_login_5 0xED863C488BF15E4EA5CB677DCA0F7798 (8 row(s) affected)
mcbath..sysusers: name sid
test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8 (6 row(s) affected)
For users to log in, they need to have the syslogins, sysusers, and SID numbers in the tables match up. Here's an example of what a successful restore looks like:
master..syslogins: name sid
BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01 test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8
mcbath..sysusers: name sid
test_login 0x80559A87BC2B7B49BA43EF92B6EDF87E test_login_1 0x85B6F41D4C681847B475097DAC1BA085 test_login_2 0x89EBE069EAC0614EA33F8C4EA283C889 test_login_3 0xC657B3EC122ED64883F517CA717728F7 test_login_4 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 test_login_5 0xE91D1C6414BE4C4E8CD9EB6502609BB8
You can fix these errors by several methods:
Drop all users from the user database. Drop the logins from the server. Then, recreate them either by a script that you had ready for such an occasion or enter them in manually. This method is the least risky, but takes the most time.
Insert the missing logins into the server, and then update the system tables via an update statement that synchronizes the SID numbers. This is higher risk.
Run a script that creates the logins in the server based on the users in the database, and then resynchronizes the SID numbers via system stored procedures and/or scripts. This is probably the fastest and safest method.
Regardless, users will get new passwords. You just need to decide if they are going to be NULL or fabricated. For security reasons, I suggest random passwords.