Makale Özeti

Amacımız DB lerimizi başka Instance'lara ve Server'lara taşırken Loginlerimizide olduğu gibi taşımak. herhangi bir Migration süreci olabilir. HA süreci veya Disaster Recovery sürecinde sıklıkla işimize yarayacak bir konu ve çözüm aşaması makelemizde yer almkatadır.

Makale

Merhaba Sevgili Arkadaşlar,

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.

CREATE LOGIN [sql_turkiye]
       WITH PASSWORD=N'1',
       DEFAULT_DATABASE=[master],
       CHECK_EXPIRATION=OFF,
       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.

USE SQLTURKIYE_
GO
IF OBJECT_ID('spk_hx') IS NOT NULL
  DROP PROCEDURE spk_hx
GO
CREATE PROCEDURE spk_hx
    @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 spk_loginscript @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
SET @tmpstr = '/* spk_loginscript script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT(varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN( 'G', 'U'))
    BEGIN
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN
       
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC spk_hx@PWD_varbinary, @PWD_string OUT
        EXEC spk_hx@SID_varbinary,@SID_string OUT
 
        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
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
 
        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
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN
      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
 

Bu sp_ leri benim DB’im 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 ;

exec 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.

 

Teşekkürler.

Diğer makalelerde görüşmek dileğiyle.

 Saygılarımla

Yusuf KAHVECİ

Database Administrator

(İzin Alınmadığı Sürece Makalelerin Yayınlanması Yasaktır! )