Database lerimizi başka instance veya sunucuya taşıma sırasında login bilgilerimizi de taşımalıyız ki üzerinde çalışan uygulamalarımızın ayarlarını yaptıktan sonra sorunsuz bir şekilde çalışmaya devam edebilsinler.
Bunu aşama aşama nasıl yaparız onu göreceğiz şimdi ;
Bu senaryoya geçmeden ilk önce Login oluşturalım.
1CREATE LOGIN [sql_turkiye]
2 WITH PASSWORD=N'1',
3 DEFAULT_DATABASE=[master],
4 CHECK_EXPIRATION=OFF,
5 CHECK_POLICY=OFF
İstersek yukarıda ki script ile Login oluşturabiliriz ya da SSMS üzerinde de kolayca Login Create edebiliz.
Yukarıdaki Scripti inceleyecek olursak ;
CREATE LOGIn den sonra loginimizin adını belirliyoruz ve WITH PASSWORD alanında ise loginimzin şifresini atıyoruz. DEFAULT_DATABASE bölümünde ise loginin default db sini belirtiyoruz. Bunun akabinde gelen CHECK_EXPIRATION=OFF şifrenin expire olmaması için. CHECK_POLICY=OFF ise şifre için policy kontrolü yapılmasını istemediğimiz için OFF yapılmıştır.
Şimdide SSMS üzerinde nasıl Login Create edebilirsiniz adım adım onu göreceğiz.
İlk önce Security sekmesi altında Logins bölümüne gelip Şekil 1.0 olduğu gibi.
Şekil 1.0
Faremizin sağ tuşuna tıklayıp New Login sekmesine basıyoruz. ( Şekil 1.1)
Bu işlemlerden sonra önümüze aşağıda ki ekran gelmektedir.Peki bu ekran da scriptimiz da belirttiğimiz tüm ayarlamalar mevcuttur göreceğiniz üzere. ( Şekil 1.2)
Yukarıda ki ayarlamaları yaptığımız da son olarak OK butonuna basarak aşağıda ki gibi loginimizi oluşturmuş oluyoruz. ( Şekil 2.0)
Evet arkadaşlar bu şekilde biz Login işlemimizi gerçekleştirmiş olduk.
Serverımıza bağlanmaya çalıştığımız da da gördüğünüz üzere bu işlemi gerçekleştirmiş olduk. (Şekil 3.0 ve 3.1 )
Şekil (3.0)
Şekil (3.1)
Bunun üzerine şimdi senaryomuza devasm edelim. Ben Sistemimi başka bir Instance a taşıyacağım ve loginlerimide taşımam gerekiyor. Peki bunu nasıl gerçekleştireceğim ?
Aşağıda hazırlamış olduğumuz script ile adım adım bu işlemlerimizi gerçekleştireceğiz.
001USE SQLTURKIYE_
002GO
003IF OBJECT_ID('spk_hx') IS NOT NULL
004 DROP PROCEDURE spk_hx
005GO
006CREATE PROCEDURE spk_hx
007 @binvalue varbinary(256),
008 @hexvalue varchar (514) OUTPUT
009AS
010DECLARE @charvalue varchar (514)
011DECLARE @i int
012DECLARE @length int
013DECLARE @hexstring char(16)
014SELECT @charvalue = '0x'
015SELECT @i = 1
016SELECT @length = DATALENGTH(@binvalue)
017SELECT @hexstring = '0123456789ABCDEF'
018WHILE (@i <= @length)
019BEGIN
020 DECLARE @tempint int
021 DECLARE @firstint int
022 DECLARE @secondint int
023 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
024 SELECT @firstint = FLOOR(@tempint/16)
025 SELECT @secondint = @tempint -(@firstint*16)
026 SELECT @charvalue = @charvalue +
027 SUBSTRING(@hexstring, @firstint+1, 1) +
028 SUBSTRING(@hexstring, @secondint+1, 1)
029 SELECT @i = @i + 1
030END
031
032SELECT @hexvalue = @charvalue
033GO
034
035IF OBJECT_ID('sp_help_revlogin') IS NOT NULL
036 DROP PROCEDURE sp_help_revlogin
037GO
038CREATE PROCEDURE spk_loginscript @login_name sysname = NULL AS
039DECLARE @name sysname
040DECLARE @type varchar (1)
041DECLARE @hasaccess int
042DECLARE @denylogin int
043DECLARE @is_disabled int
044DECLARE @PWD_varbinary varbinary (256)
045DECLARE @PWD_string varchar (514)
046DECLARE @SID_varbinary varbinary (85)
047DECLARE @SID_string varchar (514)
048DECLARE @tmpstr varchar (1024)
049DECLARE @is_policy_checked varchar (3)
050DECLARE @is_expiration_checked varchar (3)
051
052DECLARE @defaultdb sysname
053
054IF (@login_name IS NULL)
055 DECLARE login_curs CURSOR FOR
056
057 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
058sys.server_principals p LEFT JOIN sys.syslogins l
059 ON ( l.name = p.name ) WHERE p.type IN( 'S', 'G', 'U' ) AND p.name <> 'sa'
060ELSE
061 DECLARE login_curs CURSOR FOR
062
063
064 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
065sys.server_principals p LEFT JOIN sys.syslogins l
066 ON ( l.name = p.name ) WHERE p.type IN( 'S', 'G', 'U' ) AND p.name = @login_name
067OPEN login_curs
068
069FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
070IF (@@fetch_status = -1)
071BEGIN
072 PRINT 'No login(s) found.'
073 CLOSE login_curs
074 DEALLOCATE login_curs
075 RETURN -1
076END
077SET @tmpstr = '/* spk_loginscript script '
078PRINT @tmpstr
079SET @tmpstr = '** Generated ' + CONVERT(varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
080PRINT @tmpstr
081PRINT ''
082WHILE (@@fetch_status <> -1)
083BEGIN
084 IF (@@fetch_status <> -2)
085 BEGIN
086 PRINT ''
087 SET @tmpstr = '-- Login: ' + @name
088 PRINT @tmpstr
089 IF (@type IN( 'G', 'U'))
090 BEGIN
091 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
092 END
093 ELSE BEGIN
094
095 SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
096 EXEC spk_hx@PWD_varbinary, @PWD_string OUT
097 EXEC spk_hx@SID_varbinary,@SID_string OUT
098
099 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
100 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
101
102 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
103
104 IF ( @is_policy_checked IS NOT NULL )
105 BEGIN
106 SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
107 END
108 IF ( @is_expiration_checked IS NOT NULL )
109 BEGIN
110 SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
111 END
112 END
113 IF (@denylogin = 1)
114 BEGIN
115 SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
116 END
117 ELSE IF (@hasaccess = 0)
118 BEGIN
119 SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
120 END
121 IF (@is_disabled = 1)
122 BEGIN
123 SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
124 END
125 PRINT @tmpstr
126 END
127
128 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
129 END
130CLOSE login_curs
131DEALLOCATE login_curs
132RETURN 0
133GO
Bu sp_ leri benim DBim de oluşturdum. Sizde kendinize bu işler için bir DB create edip bu tarz işlemlerinizi onun üzerinde gerçekleştirmenizi tavsiye ederim.
Bu aşamadan sonra ;
1exec spk_loginscript
sp_ mizi çalıştırdıktan sonra bize login create scriptlerimiz ekrana yansımaktadır.
Şekil (4.0)
Bu scripti alıp DB lerimizi taşıdığımız Instance da EXECUTE ediyoruz.
Şekil (4.1)
Yukarıda da görüldüğü üzere bu işlem sağlıklı bir şekilde gerçekleşti. Şimdi bu işlemi takiben Instance mızda login olmayı deniyelim.
Şekil (5.0)
Şekil (5.1)
Login işlemlerimizin gerçekleştiğinide test ettiğimize göre login lerimizi taşımış olduk. İster bizim gibi istediğiniz loginleri isterse hepsini aynı şekilde taşıya bilirsiniz.
Bunu aşama aşama nasıl yaparız onu göreceğiz şimdi ;
Bu senaryoya geçmeden ilk önce Login oluşturalım.
1CREATE LOGIN [sql_turkiye]
2 WITH PASSWORD=N'1',
3 DEFAULT_DATABASE=[master],
4 CHECK_EXPIRATION=OFF,
5 CHECK_POLICY=OFF
İstersek yukarıda ki script ile Login oluşturabiliriz ya da SSMS üzerinde de kolayca Login Create edebiliz.
Yukarıdaki Scripti inceleyecek olursak ;
CREATE LOGIn den sonra loginimizin adını belirliyoruz ve WITH PASSWORD alanında ise loginimzin şifresini atıyoruz. DEFAULT_DATABASE bölümünde ise loginin default db sini belirtiyoruz. Bunun akabinde gelen CHECK_EXPIRATION=OFF şifrenin expire olmaması için. CHECK_POLICY=OFF ise şifre için policy kontrolü yapılmasını istemediğimiz için OFF yapılmıştır.
Şimdide SSMS üzerinde nasıl Login Create edebilirsiniz adım adım onu göreceğiz.
İlk önce Security sekmesi altında Logins bölümüne gelip Şekil 1.0 olduğu gibi.
Faremizin sağ tuşuna tıklayıp New Login sekmesine basıyoruz. ( Şekil 1.1)
Bu işlemlerden sonra önümüze aşağıda ki ekran gelmektedir.Peki bu ekran da scriptimiz da belirttiğimiz tüm ayarlamalar mevcuttur göreceğiniz üzere. ( Şekil 1.2)
Yukarıda ki ayarlamaları yaptığımız da son olarak OK butonuna basarak aşağıda ki gibi loginimizi oluşturmuş oluyoruz. ( Şekil 2.0)
Evet arkadaşlar bu şekilde biz Login işlemimizi gerçekleştirmiş olduk.
Serverımıza bağlanmaya çalıştığımız da da gördüğünüz üzere bu işlemi gerçekleştirmiş olduk. (Şekil 3.0 ve 3.1 )
Bunun üzerine şimdi senaryomuza devasm edelim. Ben Sistemimi başka bir Instance a taşıyacağım ve loginlerimide taşımam gerekiyor. Peki bunu nasıl gerçekleştireceğim ?
Aşağıda hazırlamış olduğumuz script ile adım adım bu işlemlerimizi gerçekleştireceğiz.
001USE SQLTURKIYE_
002GO
003IF OBJECT_ID('spk_hx') IS NOT NULL
004 DROP PROCEDURE spk_hx
005GO
006CREATE PROCEDURE spk_hx
007 @binvalue varbinary(256),
008 @hexvalue varchar (514) OUTPUT
009AS
010DECLARE @charvalue varchar (514)
011DECLARE @i int
012DECLARE @length int
013DECLARE @hexstring char(16)
014SELECT @charvalue = '0x'
015SELECT @i = 1
016SELECT @length = DATALENGTH(@binvalue)
017SELECT @hexstring = '0123456789ABCDEF'
018WHILE (@i <= @length)
019BEGIN
020 DECLARE @tempint int
021 DECLARE @firstint int
022 DECLARE @secondint int
023 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
024 SELECT @firstint = FLOOR(@tempint/16)
025 SELECT @secondint = @tempint -(@firstint*16)
026 SELECT @charvalue = @charvalue +
027 SUBSTRING(@hexstring, @firstint+1, 1) +
028 SUBSTRING(@hexstring, @secondint+1, 1)
029 SELECT @i = @i + 1
030END
031
032SELECT @hexvalue = @charvalue
033GO
034
035IF OBJECT_ID('sp_help_revlogin') IS NOT NULL
036 DROP PROCEDURE sp_help_revlogin
037GO
038CREATE PROCEDURE spk_loginscript @login_name sysname = NULL AS
039DECLARE @name sysname
040DECLARE @type varchar (1)
041DECLARE @hasaccess int
042DECLARE @denylogin int
043DECLARE @is_disabled int
044DECLARE @PWD_varbinary varbinary (256)
045DECLARE @PWD_string varchar (514)
046DECLARE @SID_varbinary varbinary (85)
047DECLARE @SID_string varchar (514)
048DECLARE @tmpstr varchar (1024)
049DECLARE @is_policy_checked varchar (3)
050DECLARE @is_expiration_checked varchar (3)
051
052DECLARE @defaultdb sysname
053
054IF (@login_name IS NULL)
055 DECLARE login_curs CURSOR FOR
056
057 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
058sys.server_principals p LEFT JOIN sys.syslogins l
059 ON ( l.name = p.name ) WHERE p.type IN( 'S', 'G', 'U' ) AND p.name <> 'sa'
060ELSE
061 DECLARE login_curs CURSOR FOR
062
063
064 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
065sys.server_principals p LEFT JOIN sys.syslogins l
066 ON ( l.name = p.name ) WHERE p.type IN( 'S', 'G', 'U' ) AND p.name = @login_name
067OPEN login_curs
068
069FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
070IF (@@fetch_status = -1)
071BEGIN
072 PRINT 'No login(s) found.'
073 CLOSE login_curs
074 DEALLOCATE login_curs
075 RETURN -1
076END
077SET @tmpstr = '/* spk_loginscript script '
078PRINT @tmpstr
079SET @tmpstr = '** Generated ' + CONVERT(varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
080PRINT @tmpstr
081PRINT ''
082WHILE (@@fetch_status <> -1)
083BEGIN
084 IF (@@fetch_status <> -2)
085 BEGIN
086 PRINT ''
087 SET @tmpstr = '-- Login: ' + @name
088 PRINT @tmpstr
089 IF (@type IN( 'G', 'U'))
090 BEGIN
091 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
092 END
093 ELSE BEGIN
094
095 SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
096 EXEC spk_hx@PWD_varbinary, @PWD_string OUT
097 EXEC spk_hx@SID_varbinary,@SID_string OUT
098
099 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
100 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
101
102 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
103
104 IF ( @is_policy_checked IS NOT NULL )
105 BEGIN
106 SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
107 END
108 IF ( @is_expiration_checked IS NOT NULL )
109 BEGIN
110 SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
111 END
112 END
113 IF (@denylogin = 1)
114 BEGIN
115 SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
116 END
117 ELSE IF (@hasaccess = 0)
118 BEGIN
119 SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
120 END
121 IF (@is_disabled = 1)
122 BEGIN
123 SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
124 END
125 PRINT @tmpstr
126 END
127
128 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
129 END
130CLOSE login_curs
131DEALLOCATE login_curs
132RETURN 0
133GO
Bu sp_ leri benim DBim de oluşturdum. Sizde kendinize bu işler için bir DB create edip bu tarz işlemlerinizi onun üzerinde gerçekleştirmenizi tavsiye ederim.
Bu aşamadan sonra ;
1exec spk_loginscript
sp_ mizi çalıştırdıktan sonra bize login create scriptlerimiz ekrana yansımaktadır.
Bu scripti alıp DB lerimizi taşıdığımız Instance da EXECUTE ediyoruz.
Şekil (4.1)
Yukarıda da görüldüğü üzere bu işlem sağlıklı bir şekilde gerçekleşti. Şimdi bu işlemi takiben Instance mızda login olmayı deniyelim.
Login işlemlerimizin gerçekleştiğinide test ettiğimize göre login lerimizi taşımış olduk. İster bizim gibi istediğiniz loginleri isterse hepsini aynı şekilde taşıya bilirsiniz.
