Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account