/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/
set nocount on
go

use master
go

CREATE PROCEDURE #convstring 
	@string varchar (255),
	@res varchar (255) output
AS 
declare @ind tinyint
declare @sym char (1)

if @string is null
	begin
	select @res = 'null'
	return 0
	end

select @ind = 1
select @res = @string

while @ind <= datalength (@res)
	begin
	select @sym = substring (@res, @ind, 1)

	if @sym = ''''
		begin
		select @res = substring (@res, 1, @ind) + '''' + 
			substring (@res, @ind + 1, datalength (@res) - 1)
		select @ind = @ind + 1
		end
	select @ind = @ind + 1
	end 

select @res = '''' + substring(@res,1,253) + ''''

RETURN
go

print ''
print 'RESET'
print '-- Syslogins --'
print ''

print 'CREATE PROCEDURE #addlogin'
print '@name varchar (30),'
print '@password varchar(30),'
print '@dbname varchar (30),'
print '@language varchar (30)'
print 'as'
print 'if not exists (select * from sysxlogins where name = @name)'
print '   exec sp_addlogin @name, @password, @dbname, @language, null, skip_encryption_old'
print 'return'
print 'go'
print ''
	
declare curLogins cursor
for select name, password, dbname, language from syslogins
where not exists (select * from upg_loginmap where sqlname = name)
	and name <> 'probe'
for read only
open curLogins

declare @name varchar (30),
	@password varchar (30),
	@dbname varchar (30),
	@language varchar (30),
	@name1 varchar (100),
	@password1 varchar (100),
	@dbname1 varchar (100),
	@language1 varchar (100),
	@line varchar (255)

while 1 = 1
	begin
	fetch curLogins into @name, @password, @dbname, @language
	if @@fetch_status <> 0 break

	if @language is null select @language = 'us_english'
	else if @language = 'japanese' select @language = 'Japanese'

	exec #convstring @name, @name1 output
	exec #convstring @password, @password1 output
	exec #convstring @dbname, @dbname1 output
	exec #convstring @language, @language1 output

	if @name <> 'sa'
		begin
		select @line = 'execute #addlogin ' + @name1 + ', ' + @password1 + ',' + @dbname1 + ', ' + @language1
		print @line
		print 'go'
		print ''
		end
	end

deallocate curLogins
go

print ''
print '-- xp_logininfo --'
print ''
print 'CREATE PROCEDURE #grantlogin'
print '@sid varbinary (85),'
print '@priv varchar (20)'
print 'as'
print 'declare @name varchar(255)'
print 'select @name = suser_sname(@sid)'
print 'if (@name is not null) and (suser_sid(@name) is not null)'
print '   begin'
print '   exec sp_grantlogin @name'
print '   if @priv = ''admin'''
print '      exec sp_addsrvrolemember @name, sysadmin'
print '   end'
print 'return'
print 'go'
print ''
print 'CREATE PROCEDURE #localgroup'
print '@name varchar (171),'
print '@priv varchar (20)'
print 'as'
print 'declare @fullname varchar(255)'
print 'select @fullname = @@servername + ''\'' + @name'
print 'exec sp_grantlogin @fullname'
print 'if @priv = ''admin'''
print '   exec sp_addsrvrolemember @fullname, sysadmin'
print 'return'
print 'go'
print ''

declare curLogininfo cursor for select sid, privilege from t_logininfo
for read only
open curLogininfo

declare @sid varchar (172),
	@privilege varchar (20),
	@line varchar (255)

while 1 = 1
	begin
	fetch curLogininfo into @sid, @privilege
	if @@fetch_status <> 0 break

	if substring(@sid, 1, 1) = '.'
		select @line = 'exec #localgroup ' + substring(@sid, 2, 255) + ', ''' + @privilege + ''''
	else
		select @line = 'exec #grantlogin ' + @sid + ', ''' + @privilege + ''''
	print @line

	print 'go'
	print ''
	end

deallocate curLogininfo
drop table t_logininfo
go

print ''
print '-- sp_defaultdb, sp_defaultlanguage --'
print ''

declare curIntLogins cursor
for select distinct ntname, dbname, language from syslogins l, upg_loginmap m
where m.sqlname = l.name
for read only
open curIntLogins

declare @ntname varchar (255),
	@dbname varchar (30),
	@language varchar (30),
	@ntname1 varchar (255),
	@dbname1 varchar (100),
	@language1 varchar (100),
	@line varchar (255)

while 1 = 1
	begin
	fetch curIntLogins into @ntname, @dbname, @language
	if @@fetch_status <> 0 break

	if @language is null select @language = 'us_english'
	else if @language = 'japanese' select @language = 'Japanese'

	exec #convstring @ntname, @ntname1 output
	exec #convstring @dbname, @dbname1 output
	exec #convstring @language, @language1 output

	select @line = 'execute sp_defaultdb ' + @ntname1 + ', ' + @dbname1
	print @line
	select @line = 'execute sp_defaultlanguage ' + @ntname1 + ', ' + @language1
	print @line
	print 'go'
	print ''
	end

deallocate curIntLogins
go

create proc #srvopt
@name varchar(100),
@opt varchar(100)
as
declare @line varchar(255)

select @line = 'exec sp_serveroption ' + @name + ', ' + @opt + ', true'
print @line

return
go

print ''
print '-- Sysservers --'
print ''

print 'CREATE PROCEDURE #addserver'
print '@name varchar (30)'
print 'as'
print 'if not exists (select * from sysservers where srvname = @name)'
print '   exec sp_addserver @name'
print 'return'
print 'go'
print ''
	
declare curServers cursor
for select srvname, srvstatus from sysservers where srvid <> 0
for read only
open curServers

declare 
	@name varchar (30),
	@name1 varchar (100),
	@status smallint,
	@line varchar (255)

while 1 = 1
	begin
	fetch curServers into @name, @status
	if @@fetch_status <> 0 break

	exec #convstring @name, @name1 output

	select @line = 'exec #addserver ' + @name1
	print @line

	if (@status & 1) = 0
		begin
		select @line = 'exec sp_serveroption ' + @name1 + ', rpc, false'
		print @line
		end

	if (@status & 2) <> 0
		exec #srvopt @name1, 'pub'
		
	if (@status & 4) <> 0
		exec #srvopt @name1, 'sub'
		
	if (@status & 8) <> 0
		exec #srvopt @name1, 'dist'
		
	if (@status & 16) <> 0
		exec #srvopt @name1, 'dpub'
	 
	print 'go'
	print ''
	end

deallocate curServers
go

print ''
print '-- Sysremotelogins --'
print ''
print 'CREATE PROCEDURE #addremotelogin '
print '@srvname varchar (30),'
print '@localuser varchar (30),'
print '@remoteuser varchar (30),'
print '@trusted smallint'
print 'AS '
print 'if @localuser is null and @remoteuser is not null'
print '   return'
print ''
print 'if @remoteuser is null'
print '   begin'
print '   if not exists'
print '      (select * from sysservers s, sysremotelogins r'
print '         where s.srvid = r.remoteserverid and'
print '               s.srvname = @srvname and'
print '               r.remoteusername is null)'
print '      exec sp_addremotelogin @srvname, @localuser'
print '   end'
print '   else'
print '   begin'
print '      -- Remote name is present'
print '      if not exists'
print '         (select * from sysservers s, sysremotelogins r'
print '            where s.srvid = r.remoteserverid and'
print '                  s.srvname = @srvname and'
print '                  r.remoteusername = @remoteuser)'
print '         exec sp_addremotelogin @srvname, @localuser, @remoteuser'
print '   end'
print ''
print 'if @trusted = 1'
print '   exec sp_remoteoption @srvname, @localuser, @remoteuser, trusted, true'
print ''
print 'return'
print 'go'
print ''

declare curRL cursor
for select
		srv.srvname,
		rl.remoteusername,
		rl.suid,
		rl.status
	from sysservers srv, sysremotelogins rl
	where srv.srvid = rl.remoteserverid
		and srv.srvid <> 0
for read only
declare @srvname varchar (30),
	@srvname1 varchar (100),
	@remotename varchar (30),
	@remotename1 varchar (30),
	@suid smallint,
	@status smallint,
	@localuser varchar (30),
	@localuser1 varchar (100)

open curRL

declare @line varchar (255)

while 1 = 1
	begin
	fetch curRL into @srvname, @remotename, @suid, @status
	if @@fetch_status <> 0 break

	select @localuser = null
	if @suid <> -1
		begin
		select @localuser = name from syslogins where suid = @suid
		if @localuser is null or @localuser = 'probe' continue
		end

	exec #convstring @srvname, @srvname1 output
	exec #convstring @remotename, @remotename1 output
	exec #convstring @localuser, @localuser1 output

	select @line = 'execute #addremotelogin ' + @srvname1 + ', ' + @localuser1 + ', ' +
		@remotename1 + ', ' + convert (varchar (30), @status)
	print @line
	print 'go'
	print ''
	end

deallocate curRL
go

print ''
print '-- Sysmessages --'
print ''
print 'CREATE PROCEDURE #altermessage'
print '@error int,'
print '@withlog varchar(20)'
print 'as'
print 'if exists (select * from sysmessages where error = @error)'
print '   exec sp_altermessage @error, WITH_LOG, @withlog'
print 'return'
print 'go'
print ''
	

declare curMsg cursor
for select msg.error, msg.severity, msg.dlevel, msg.description, lng.name
	from sysmessages msg, syslanguages lng
	where msg.langid *= lng.langid
for read only
declare @error int,
	@severity smallint,
	@dlevel smallint,
	@description varchar (255),
	@description1 varchar (255),
	@langname varchar(30),
	@langname1 varchar(255),
	@withlog int,
	@withlog1 varchar(30)

open curMsg

declare @line varchar (255)

while 1 = 1
	begin
	fetch curMsg into @error, @severity, @dlevel, @description, @langname
	if @@fetch_status <> 0 break

	if @dlevel & 0x80 = 0x80
		begin
		select @withlog = 1
		select @withlog1 = 'TRUE'
		end
	else
		begin
		select @withlog = 0
		select @withlog1 = 'FALSE'
		end

	if @error < 50000
		begin
		if (@withlog = 1 and @error != 1105) or (@withlog = 0 and @error = 1105)
			begin
			select @line = 'exec #altermessage ' + convert(varchar(30), @error) + ', ' + @withlog1
			print @line
			print 'go'
			print ''
			end
		end
	else
		begin

		if @langname is null select @langname = 'us_english'
		else if @langname = 'japanese' select @langname = 'Japanese'

		exec #convstring @description, @description1 output
		exec #convstring @langname, @langname1 output

		select @line = 'sp_addmessage ' + convert(varchar(30), @error) + ', ' + convert(varchar(30), @severity) + ', '
		print @line
		print @description1
		select @line = ', ' + @langname1 + ', ' + @withlog1 + ', REPLACE'
		print @line
		print 'go'
		print ''
		end
	end

deallocate curMsg
go

print ''
print '-- Transfer upg_loginmap --'
print ''
print 'if exists (select * from sysobjects where name=''upg_loginmap'')'
print '      drop table upg_loginmap'
print 'go'
print ''
print 'create table upg_loginmap'
print '      (sqlname varchar(30),'
print '      ntname varchar(255))'
print 'go'
print ''

declare curLoginmap cursor
for select sqlname, ntname from upg_loginmap
for read only
open curLoginmap

declare 
	@sqlname varchar (30),
	@sqlname1 varchar (100),
	@ntname varchar(255),
	@ntname1 varchar (255),
	@line varchar(255)

while 1 = 1
	begin
	fetch curLoginmap into @sqlname, @ntname
	if @@fetch_status <> 0 break

	exec #convstring @sqlname, @sqlname1 output
	exec #convstring @ntname, @ntname1 output

	select @line = 'insert upg_loginmap values(' + @sqlname1 + ', '
	print @line
	print @ntname1
	print ')'
	print 'go'

	end

deallocate curLoginmap
go

