Home > Articles > Data > SQL Server

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

  • + Share This
  • 🔖 Save To Your Account