SERVER TASIMASI MIGRATION ROAD MAP AND EXAMPLE ICINDE TUM SCRIPT
Definition Description
-- 1)ESKI 1.5 SERVER LOGINLERI LISTELE VE [DBACHECKDB].DBO.SERVERLOGINS TABLOSUNA INSERT ET
CREATE TABLE [DBACHECKDB].dbo.ServerLogins( [name] [sysname] NOT NULL, LogDate DATETIME NOT NULL DEFAULT getdate() ) ON [PRIMARY]
TRUNCATE TABLE [DBACHECKDB].dbo.ServerLogins
INSERT INTO [DBACHECKDB].dbo.ServerLogins (LoginName) SELECT l.name FROM master..syslogins l INNER JOIN sys.databases d on l.dbname=d.name WHERE l.name NOT LIKE '%#%' --AND l.name NOT LIKE '%\%' AND l.name NOT IN('sa','admin','guest') AND d.is_read_only=0
DECLARE cur CURSOR FOR SELECT name FROM sys.databases
OPEN cur FETCH NEXT FROM cur INTO @database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'SELECT '''+@database+''', l.loginname as [login name],u.name as [user name] FROM '+@database+'..sysusers u INNER JOIN master..syslogins l on u.sid=l.sid'
INSERT INTO #logins_users EXEC (@cmd) FETCH NEXT FROM cur INTO @database END CLOSE cur DEALLOCATE cur go --SELECT * FROM #logins_users CREATE TABLE [DBACHECKDB].dbo.ServerLoginsMapping( [username] [varchar](100) NULL, [database_name] [varchar](100) NULL, LogDate DATETIME NOT NULL DEFAULT getdate()
) ON [PRIMARY]
TRUNCATE TABLE [DBACHECKDB].dbo.ServerLoginsMapping INSERT INTO [DBACHECKDB].dbo.ServerLoginsMapping (username,DatabaseName) SELECT distinct (username),[database_name] FROM #logins_users WHERE username NOT LIKE '%##%' --AND username NOT LIKE '%\%' AND username<>'sa' GROUP BY username,database_name ORDER BY username,database_name
DECLARE UserAuthentication_Cur cursor fast_forward for SELECT [name] DBName FROM sys.databases WHERE [name] NOT IN ('master','tempdb','model','msdb','distribution') ORDER BY [name] OPEN UserAuthentication_Cur; FETCH NEXT FROM UserAuthentication_Cur INTO @DBName WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute=+CHAR(13)+CHAR(10)+ +CHAR(13)+CHAR(10)+'use '+'['+@DBName+']'+CHAR(13)+CHAR(10) +'INSERT INTO [[DBACHECKDB]].[dbo].[UserAuthentication] (ObjectType, Asmajor_id, ServerName, DatabaseName, PermissionDesc, SchemaName, ObjectName, type_desc, Grantee, Grantor, principal_type_desc, permission_name, permission_state_desc) SELECT dperm.class_desc AS [ObjectType], dperm.major_id [Asmajor_id] , @@SERVERNAME AS [ServerName], DB_NAME() AS [DatabaseName], CASE WHEN o.type_desc IS NOT NULL THEN ''Permission on OBJECT (TABLE,SP,FUNC..) level'' ELSE ''Permission on Database Level'' END AS [PermissionDesc], ISNULL(sch.name, osch.name) AS [SchemaName], ISNULL(o.name, ''.'') AS [ObjectName], o.type_desc AS [type_desc], dprin.NAME AS [Grantee], grantor.name AS [Grantor], dprin.type_desc AS [principal_type_desc], dperm.permission_name AS [permission_name], dperm.state_desc AS [permission_state_desc] FROM '+@DBName+'.sys.database_permissions dperm INNER JOIN '+@DBName+'.sys.database_principals dprin ON dperm.grantee_principal_id = dprin.principal_id INNER JOIN '+@DBName+'.sys.database_principals grantor ON dperm.grantor_principal_id = grantor.principal_id LEFT OUTER JOIN '+@DBName+'.sys.schemas sch ON dperm.major_id = sch.schema_id AND dperm.class = 3 LEFT OUTER JOIN '+@DBName+'.sys.all_objects o ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1 LEFT OUTER JOIN '+@DBName+'.sys.schemas osch ON o.schema_id = osch.schema_id WHERE dprin.name <> ''public'' AND dperm.type <> ''CO'' AND dprin.type <> ''R'' AND dprin.NAME <>''guest'' ORDER BY 1, 2, 3, 5 '+CHAR(13)+CHAR(10) EXEC (@CommandToExecute) --PRINT (@CommandToExecute) FETCH NEXT FROM UserAuthentication_Cur INTO @DBName END CLOSE UserAuthentication_Cur; DEALLOCATE UserAuthentication_Cur;
-- 5)SERVER BAZINDA LOGIN BILGISINI ESKI 1.5 DEN LOGINLERI; ASAGIDAKI SCRIPTI CALISTIRILARAK RESULT SETTEKI SCRIPTLER 1.8 CALISITIRLIR.
USE [DBACHECKDB] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue VARBINARY(256), @hexvalue VARCHAR (514) OUTPUT AS DECLARE @CHARvalue VARCHAR (514) DECLARE @i int DECLARE @length int DECLARE @hexstring CHAR(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END
SELECT @hexvalue = @charvalue GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type VARCHAR (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary VARBINARY (256) DECLARE @PWD_string VARCHAR (514) DECLARE @SID_varbinary VARBINARY (85) DECLARE @SID_string VARCHAR (514) DECLARE @tmpstr VARCHAR (1024) DECLARE @is_policy_checked VARCHAR (3) DECLARE @is_expiration_checked VARCHAR (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END
WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS VARBINARY (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login EXISTS but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO
-- 7) ÖNCELİKLE MASTER TARAFTAKİ DATABASELERİ ve DBCHECKDB backup alın ve DİĞER TARAFA RESTORE EDİN, ÇÜNKÜ YUKARIDA YAPILAN İŞLEMLERİ BU DATABASE STORE ETTİK
-- 8) Eski SERVER üzerndeki roller yeni server üzerine assign edilir. [DBACHECKDB].[dbo].[RoleMemberList] DECLARE @ServerRole VARCHAR(100) DECLARE @MemberName VARCHAR(200) DECLARE @MemberSID VARCHAR(100) DECLARE @CommandToExecute1 VARCHAR(1000)
DECLARE roleAssign CURSOR FAST_FORWARD FOR SELECT R.ServerRole, R.MemberName FROM [DBACHECKDB].dbo.RoleMemberList R INNER JOIN sys.server_principals P ON R.MemberSID = P.[sid] WHERE R.MemberName != 'sa'
OPEN roleAssign; FETCH NEXT FROM roleAssign INTO @ServerRole, @MemberName WHILE @@FETCH_STATUS = 0 BEGIN SET @CommandToExecute1 = 'ALTER SERVER ROLE ['+@ServerRole+'] ADD MEMBER ['+@MemberName+']' PRINT (@CommandToExecute1) -- EXEC (@CommandToExecute1) FETCH NEXT FROM roleAssign INTO @ServerRole, @MemberName END CLOSE roleAssign; DEALLOCATE roleAssign;
-- 9) YENI 1.5(1.8) USERLAR DATABASE UZERINDEN VARSA DROP EDILIR VE TEKRAR CREATE EDILIREK MAP LENIR
DECLARE loginMapping_cur CURSOR FAST_FORWARD FOR SELECT DatabaseName,username FROM [DBACHECKDB].dbo.ServerLoginsMapping OPEN loginMapping_cur; FETCH NEXT FROM loginMapping_cur INTO @DBName,@login
WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute=+CHAR(13)+CHAR(10) +'USE '+ @DBName+ +CHAR(13)+CHAR(10) +'IF EXISTS(SELECT NAME FROM SYSUSERS WHERE NAME = '''+@login+''')' +CHAR(13)+CHAR(10) + 'BEGIN' +CHAR(13)+CHAR(10) +' DROP USER ['+@login+']' +CHAR(13)+CHAR(10) +'END' +CHAR(13)+CHAR(10) +'CREATE USER ['+@login+'] FOR LOGIN ['+@login+']' PRINT (@CommandToExecute) --EXEC (@CommandToExecute) FETCH NEXT FROM loginMapping_cur INTO @DBName,@login END CLOSE loginMapping_cur; DEALLOCATE loginMapping_cur;
GO sp_helprole -------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
----10) DATABASE UZERINDE İZİN VERME-YETKILENDIRME: DİKKAT !!!!!!!!!!! -- BURADA KULLANICIYA DATABASE ÜZERİNDE YETKİ VERİLMEKTEDİR.!!!!!!!!!! -- Aşağıdaki sorguda EĞER MAJOR_ID = 0 ise yetkiler DATABASE leve üzerindedir !!!!!
DECLARE UserAuthenticationPermission_Cur CURSOR FAST_FORWARD FOR
SELECT DatabaseName,permission_name,Grantee FROM [DBACHECKDB].dbo.UserAuthentication WHERE major_id = 0
OPEN UserAuthenticationPermission_Cur; FETCH NEXT FROM UserAuthenticationPermission_Cur INTO @DatabaseName,@permission_name,@Grantee
WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute=+CHAR(13)+CHAR(10) +'USE '+@DatabaseName +CHAR(13)+CHAR(10) +'GRANT '+ @permission_name+ ' TO ['+@Grantee+']' +CHAR(13)+CHAR(10) PRINT (@CommandToExecute) --EXEC (@CommandToExecute)
FETCH NEXT FROM UserAuthenticationPermission_Cur INTO @DatabaseName,@permission_name,@Grantee END CLOSE UserAuthenticationPermission_Cur; DEALLOCATE UserAuthenticationPermission_Cur;
go
go /*BURADA SADECE OBJECT BAZLI USERLARI GRANT EDIYORUZ. -- Aşağıdaki sorguda EĞER MAJOR_ID = 1259 gibi bir object numarası taşıyorsa,yetkiler OBJECT(TABLE,SP,FUNC..) level üzerindedir !!!!! */ DECLARE @DatabaseName VARCHAR(100) DECLARE @schemaName VARCHAR(100) DECLARE @ObjectName VARCHAR(100) DECLARE @permission_name VARCHAR(100) DECLARE @Grantee VARCHAR(100) DECLARE @CommandToExecute VARCHAR(8000)
DECLARE UserAuthenticationPermission_Cur CURSOR FAST_FORWARD FOR SELECT DatabaseName,permission_name,SchemaName,ObjectName,Grantee FROM [DBACHECKDB].dbo.UserAuthentication WHERE major_id !=0
OPEN UserAuthenticationPermission_Cur; FETCH NEXT FROM UserAuthenticationPermission_Cur INTO @DatabaseName,@permission_name,@schemaName,@ObjectName,@Grantee
WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute=+CHAR(13)+CHAR(10) +'USE '+@DatabaseName +CHAR(13)+CHAR(10) +'GRANT '+@permission_name+' ON OBJECT::'+@schemaName+'.'+@ObjectName+' TO ['+@Grantee+']' +CHAR(13)+CHAR(10) PRINT (@CommandToExecute) --EXEC (@CommandToExecute)
FETCH NEXT FROM UserAuthenticationPermission_Cur INTO @DatabaseName,@permission_name,@schemaName,@ObjectName,@Grantee END CLOSE UserAuthenticationPermission_Cur; DEALLOCATE UserAuthenticationPermission_Cur;
