Po przeniesieniu z jednej instancji na drugą baz danych w MS SQL Server 2000 należy wykonąc kolejne czynności:
1. Przeniesienie do Security: Logins, Server Roles, User Mapping.
Na stronie Microsoftu „How to transfer logins and passwords between instances of SQL Server” znajduje się artykuł na ten temat.
Opisane są tam dwie metody:
metoda 1: przedstawia dwie procedury składowane sp_hexadecimal i sp_help_revlogin. Uruchamia się skrypt z poziomu bazy master.
Skrypt ten generuje kod tworzący loginy. Przystosowany jest on do przenoszenia loginów z wersji 2000 na wersję 2000 !!!
metoda 2: przedstawia dwie procedury składowane sp_hexadecimal i sp_help_revlogin_2000_to_2005. Uruchamia się skrypt z poziomu bazy master.
Skrypt ten generuje kod tworzący loginy, defaultowe bazy i role. Przystosowany jest on do przenoszenia loginów z wersji 2000 na wersję 2005 !!!
Po odpowiedniej modifikacji tych dwóch skryptów stworzyłem:
metoda 3: przedstawia dwie procedury składowane sp_hexadecimal i sp_help_revlogin_2000_to_2000_DBrejnak. Uruchamia się skrypt z poziomu bazy master.
Skrypt ten generuje kod tworzący loginy, defaultowe bazy i role. Przystosowany jest on do przenoszenia loginów z wersji 2000 na wersję 2000 !!!
007 | IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL |
008 | DROP PROCEDURE sp_hexadecimal |
010 | CREATE PROCEDURE sp_hexadecimal |
011 | @binvalue varbinary(256), |
012 | @hexvalue varchar(256) OUTPUT |
014 | DECLARE @charvalue varchar(256) |
017 | DECLARE @hexstring char(16) |
018 | SELECT @charvalue = '0x' |
020 | SELECT @length = DATALENGTH (@binvalue) |
021 | SELECT @hexstring = '0123456789ABCDEF' |
022 | WHILE (@i <= @length) |
026 | DECLARE @secondint int |
027 | SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) |
028 | SELECT @firstint = FLOOR(@tempint/16) |
029 | SELECT @secondint = @tempint - (@firstint*16) |
030 | SELECT @charvalue = @charvalue + |
031 | SUBSTRING(@hexstring, @firstint+1, 1) + |
032 | SUBSTRING(@hexstring, @secondint+1, 1) |
035 | SELECT @hexvalue = @charvalue |
038 | IF OBJECT_ID ('sp_help_revlogin_2000_to_2000_DBrejnak') IS NOT NULL |
039 | DROP PROCEDURE sp_help_revlogin_2000_to_2000_DBrejnak |
041 | CREATE PROCEDURE sp_help_revlogin_2000_to_2000_DBrejnak |
043 | @login_name sysname = NULL, |
050 | DECLARE @binpwd varbinary (256) |
051 | DECLARE @dfltdb varchar (256) |
052 | DECLARE @txtpwd sysname |
053 | DECLARE @tmpstr varchar (256) |
054 | DECLARE @SID_varbinary varbinary(85) |
055 | DECLARE @SID_string varchar(256) |
057 | IF (@login_name IS NULL) |
058 | DECLARE login_curs CURSOR STATIC FOR |
059 | SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') |
060 | FROM master.dbo.sysxlogins |
061 | WHERE srvid IS NULL AND |
064 | DECLARE login_curs CURSOR FOR |
065 | SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') |
066 | FROM master.dbo.sysxlogins |
067 | WHERE srvid IS NULL AND |
072 | FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
074 | IF (@@fetch_status = -1) |
076 | PRINT 'No login(s) found.' |
082 | SET @tmpstr = '/* sp_help_revlogin_2000_to_2000_DBrejnak script ' |
084 | SET @tmpstr = '** Generated ' |
085 | + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' |
088 | PRINT 'DECLARE @pwd sysname' |
089 | WHILE (@@fetch_status <> -1) |
091 | IF (@@fetch_status <> -2) |
094 | SET @tmpstr = '-- Login: ' + @name |
096 | IF (@xstatus & 4) = 4 |
098 | IF (@xstatus & 1) = 1 |
100 | SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' |
104 | SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' |
109 | IF (@binpwd IS NOT NULL) |
111 | EXEC sp_hexadecimal @binpwd, @txtpwd OUT |
112 | IF (@xstatus & 2048) = 2048 |
113 | SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' |
115 | SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' |
117 | EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT |
118 | SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name |
119 | + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' |
123 | EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT |
124 | SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name |
125 | + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' |
127 | IF (@xstatus & 2048) = 2048 |
129 | SET @tmpstr = @tmpstr + '''skip_encryption_old''' |
131 | SET @tmpstr = @tmpstr + '''skip_encryption''' |
135 | FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
145 | PRINT '/***** SET DEFAULT DATABASES *****/' |
147 | FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
149 | WHILE @@fetch_status = 0 |
152 | SET @tmpstr = '-- Login: ' + @name |
155 | SET @tmpstr = 'EXEC sp_defaultdb [' + @name + '], [' + @dfltdb + ']' |
159 | FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
168 | PRINT '/***** SET SERVER ROLES *****/' |
170 | FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
172 | WHILE @@fetch_status = 0 |
175 | SET @tmpstr = '-- Login: ' + @name |
178 | IF @xstatus &16 = 16 |
180 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' |
184 | IF @xstatus &32 = 32 |
186 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' |
190 | IF @xstatus &64 = 64 |
192 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' |
196 | IF @xstatus &128 = 128 |
198 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' |
202 | IF @xstatus &256 = 256 |
204 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' |
208 | IF @xstatus &512 = 512 |
210 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' |
214 | IF @xstatus &1024 = 1024 |
216 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' |
220 | IF @xstatus &4096 = 4096 |
222 | SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' |
226 | FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb |
235 | exec sp_help_revlogin_2000_to_2000_DBrejnak @login_name=NULL, @include_db=1, @include_role=1 |
Dodatkowe informacje są równiez na stronie Microsoft: „How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008”
2. Przeniesienie jobów:
Z poziomu Enterprise Managera w ramach wybranej instancji wchodzimy w Management, następnie wybieramy SQL Server Agent. W nim klikami ppm na Jobs a następnie wybieramy z menu podręcznego „Wszystkie zadania” i dalej „Generate SQL Script”. Po wygenerowaniu skryptu kopiujemy go i uruchamiamy na serwerze docelowym.
