␡
- 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
This chapter is from the book
Program to Generate Logins from the User Database
This is the stored procedure to create logins in the server from a restored database that had orphaned users. There are two modes you can run it in, test and run. Test mode will show you the output before it makes any changes. This way, you can check that the passwords and logins are correct. Run mode will actually execute the changes and update the system tables.
Here are the combinations in which you can run it:
exec fix_logins @random='RANDOM', @run='TEST'
exec fix_logins @random='RANDOM', @run='RUN' exec fix_logins @random='', @run='TEST' exec fix_logins @random='', @run='RUN'
If you don't specify to use random passwords, then the password will be "password." Note, the random password uses the RAND() function. For better password generation, use a better RAND() seed if you need it.
create proc fix_logins
@random varchar(20)='', @run varchar(20)='TEST' as begin set nocount on declare @no_users int declare @user_name sysname declare @dbname sysname declare @def_lang sysname declare @counter int declare @name sysname declare @sid varbinary(85) declare @pw varchar(20)
if (upper(@run) <> 'RUN') begin print '' print '**************************' print '* Test Run *' print '**************************' print '' end
select @no_users = count(*) from sysusers where sid <> 0x01 and sid <> 0x00 and sid is not NULL select @dbname = db_name(dbid) from master..sysprocesses where spid=@@spid select @def_lang =a.name from master..syslanguages a, master..sysconfigures b where b.comment = 'default language' and b.value = a.langid select @counter=1 declare sysusers_cursor cursor for select name, sid from sysusers where sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name open sysusers_cursor fetch next from sysusers_cursor into @name, @sid
while (@@FETCH_STATUS = 0) begin select @counter=@counter + 1 if (upper(@random) = 'RANDOM') begin select @pw=convert(varchar(50),RAND(@counter)) select @pw=substring(@pw,3,5) end else begin select @pw='password' end
if (upper(@run) = 'RUN') begin exec sp_addlogin @name, @pw, @dbname, @def_lang, @sid end
select 'Adding Login: ', @name, @pw, @dbname, @def_lang, @sid
fetch next from sysusers_cursor into @name, @sid end close sysusers_cursor deallocate sysusers_cursor
print 'master..syslogins:' select name, sid from master..syslogins order by name select @dbname=@dbname+'..sysusers' print '' print @dbname select name, sid from sysusers where sid <> 0x01 and sid <> 0x00 and sid is not NULL order by name set nocount off end
Here is the output when the logins and users are not synchronized between the user's database and the master database:
exec fix_logins @random='RANDOM', @run='TEST' go *************************** * Test Run * *************************** Adding Login: test_login 71361 mcbath us_english 0x80559A87BC2B7B49BA43EF92B6EDF87E Adding Login: test_login_1 71362 mcbath us_english 0x85B6F41D4C681847B475097DAC1BA085
Adding Login: test_login_2 71364 mcbath us_english 0x89EBE069EAC0614EA33F8C4EA283C889
Adding Login: test_login_3 71366 mcbath us_english 0xC657B3EC122ED64883F517CA717728F7
Adding Login: test_login_4 71368 mcbath us_english 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4
Adding Login: test_login_5 71370 mcbath us_english 0xE91D1C6414BE4C4E8CD9EB6502609BB8 master..syslogins: name sid BUILTIN\Administrators 0x01020000000000052000000020020000 sa 0x01
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
Following is the output when the logins and users have been synchronized between the user's database and the master database using the stored procedure:
exec fix_logins @random='RANDOM', @run='RUN'
go New login created. Adding Login: test_login 71361 mcbath us_english 0x80559A87BC2B7B49BA43EF92B6EDF87E New login created.
Adding Login: test_login_1 71362 mcbath us_english 0x85B6F41D4C681847B475097DAC1BA085 New login created. Adding Login: test_login_2 71364 mcbath us_english 0x89EBE069EAC0614EA33F8C4EA283C889 New login created.
Adding Login: test_login_3 71366 mcbath us_english 0xC657B3EC122ED64883F517CA717728F7 New login created.
Adding Login: test_login_4 71368 mcbath us_english 0x3E1654A4E8AA634DA5E3DAFBAFD84FA4 New login created.
Adding Login: test_login_5 71370 mcbath us_english 0xE91D1C6414BE4C4E8CD9EB6502609BB8 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
TIP
To avoid these types of ugly problems, back up your master database (which is very small) every time you back up your user database. Also, document your system very well. Have scripts that work and have been tested well in advance.