SQL Server 服务器器信息备份（一）--login新建脚本备份

前言

若你的企业使用SQL Server数据库镜像为容灾技术。

那你一定做过在镜像切换之前要新建Login，而且若Login密码不同，要修改链接数据库的字符串，在切换完之后则仍需要给数据库重新赋予权限。

若真的是这样做，不仅麻烦而且业务故障时间将会拉长，我们需要做到新建的Login与之前的密码完全一样，而且可自动根据原有数据库用户链接到Login，数据库级别权限不用从新处理。

思路

有了这样的思路则Login的信息备份需要考虑两点：

1、一定要获取用户的SID，以保证镜像切换后能自动连接到login。

2、如何将二进制类型的用户密码和SID转换成字符类型存放

    数据库存一个二进制为8Bit，每四位为一个16进制。8Bit二进制除以16则取得高4Bit的二进制，模以16则取低4Bit二进制。如下所示

Substring('0123456789ABCDEF',8bit / 16 + 1,1) + Substring('0123456789ABCDEF',8bit % 16 + 1,1)

脚本

通过以上想法笔者得出以下脚本：

脚本通过循环处理分析二进制的SID和PASSWORD得到相应的字符串，拼成相应login 的新建脚本，将脚本存放到指定文件下。

CREATE PROC  [dbo].[spm_GetLoginDetails] 
    @loginame varchar(100)=null,
    @filepath varchar(1000)=null
AS
    SET NOCOUNT ON

    DECLARE @bin_pwd VARBINARY (8000)
    DECLARE @bin_sid VARBINARY (8000)--VARBINARY密码和sid
    DECLARE @string_pwd VARCHAR(8000) 
    SET @string_pwd=''
    DECLARE @string_sid VARCHAR(8000) 
    SET @string_sid=''

    DECLARE @pwd INT,@sid INT
    /*脚本存放*/
    CREATE TABLE ##scriptall(id INT IDENTITY(1,1),line VARCHAR(MAX))

    /*如果loginame 不存在则输出所有脚本*/
    IF @loginame IS NULL 
    BEGIN
     CREATE TABLE #temp(id INT IDENTITY(1,1),name VARCHAR(100),sid VARBINARY(256),string_pwd VARCHAR(1000),string_sid VARCHAR(1000))
 
     INSERT INTO #temp(sid,name)
     SELECT sid,name FROM   sys.sql_logins
 
     DECLARE @minid INT,@maxid INT
     SELECT @minid=min(id),@maxid=MAX(id) FROM #temp

     /*VARBINARY类型转化处理*/
     WHILE (@minid<=@maxid)
      BEGIN 
            SELECT @bin_pwd = sl.password_hash,@bin_sid=sl.sid,@string_pwd='',@string_sid=''
            FROM   sys.sql_logins sl join #temp t on t.sid =sl.sid 
            where t.id=@minid

            /*密码类型转化*/ 
            SELECT  @pwd = Datalength(@bin_pwd),@sid=Datalength(@bin_sid)
            WHILE @pwd > 0
              BEGIN
                SELECT @string_pwd = Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1) / 16 + 1,1) + 
                                     Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1)%16 + 1,1) + @string_pwd
                                     ,@pwd = @pwd - 1
              END
            SELECT @string_pwd = ('0x' + @string_pwd)

           /*sid类型转化*/ 
            WHILE @sid > 0
              BEGIN
                SELECT @string_sid = Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1) / 16 + 1,1) + 
                                     Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1)%16 + 1,1) + @string_sid,
                                     @sid = @sid - 1
              END          
            SELECT @string_sid = ('0x' + @string_sid)
            UPDATE #temp SET string_pwd=@string_pwd,string_sid=@string_sid WHERE id=@minid        
            SET @minid=@minid+1        
      END
     /*导出login新建脚本*/
     INSERT INTO  ##scriptall(line)
     SELECT CASE WHEN sp.TYPE = 'S' THEN '
     /****** Object:  Login ['+sp.name+']    Script Date: '+convert(VARCHAR,GETDATE(),25)+' ******/
     CREATE LOGIN [' + sp.name + '] WITH PASSWORD = ' + t.string_pwd + '  HASHED, SID = ' + t.string_sid + ',CHECK_POLICY = ' + CASE sl.is_policy_checked WHEN 0 THEN 'OFF'ELSE 'ON'END + '  , CHECK_EXPIRATION = ' + CASE sl.is_policy_checked WHEN 0 THEN 'OFF'ELSE 'ON'END +char(10)+CASE when s.denylogin=1 then '; DENY CONNECT SQL TO ' + QUOTENAME( sp.name ) else '' end +case when  s.hasaccess=0 then '; REVOKE CONNECT SQL TO ' + QUOTENAME( sp.name )  else ''end  
     ELSE 
     '/****** Object:  Login ['+sp.name+']    Script Date: '+convert(VARCHAR,GETDATE(),25)+' ******/
     CREATE LOGIN [' + sp.name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + sp.default_database_name + ']'
     END 
      FROM   sys.sql_logins sl
               RIGHT JOIN sys.server_principals sp
                 ON sl.sid = sp.sid
               JOIN sys.syslogins s
                 ON s.sid = sp.sid
               left join #temp t on t.sid=sl.sid
      WHERE  sp.TYPE IN ('S','G','U') and loginname<>'sa'
      ORDER BY sp.name
    END
    /*若存在loginame，则输出相应的脚本*/
    ELSE 
    BEGIN 

    IF EXISTS (SELECT TOP 1 1 FROM   sys.sql_logins s  WHERE  s.name = @loginame)
      BEGIN
    
        SELECT @bin_pwd = sl.password_hash,@bin_sid=sl.sid
        FROM   sys.sql_logins sl
        WHERE  sl.name = @loginame
        SELECT  @pwd = Datalength(@bin_pwd),@sid=Datalength(@bin_sid)
        /*密码类型转化*/ 
        WHILE @pwd > 0
          BEGIN
            SELECT @string_pwd = Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1) / 16 + 1,
                                   1) + Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1)%16 + 1,
                                                  1) + @string_pwd,@pwd = @pwd - 1              
          END
        SELECT @string_pwd = ('0x' + @string_pwd)
        /*SID类型转化*/ 
        WHILE @sid > 0
          BEGIN
            SELECT @string_sid = Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1) / 16 + 1,
                                   1) + Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1)%16 + 1,
                                                  1) + @string_sid,@sid = @sid - 1
               
          END
        SELECT @string_sid = ('0x' + @string_sid)
    
      END
     /*导出login新建脚本*/
     INSERT INTO ##scriptall(line)
     SELECT CASE WHEN sp.TYPE = 'S' THEN '
     /****** Object:  Login ['+sp.name+']    Script Date: '+convert(varchar,GETDATE(),25)+' ******/
     CREATE LOGIN [' + sp.name + '] WITH PASSWORD = ' + @string_pwd + '  HASHED, SID = ' + @string_sid + ',CHECK_POLICY = ' + CASE sl.is_policy_checked WHEN 0 THEN 'OFF'ELSE 'ON'END + '  , CHECK_EXPIRATION = ' + CASE sl.is_policy_checked WHEN 0 THEN 'OFF'ELSE 'ON'END +char(10)+CASE when s.denylogin=1 then '; DENY CONNECT SQL TO ' + QUOTENAME( sp.name ) else '' end +case when  s.hasaccess=0 then '; REVOKE CONNECT SQL TO ' + QUOTENAME( sp.name )  else ''end  
     ELSE 
     '/****** Object:  Login ['+sp.name+']    Script Date: '+convert(varchar,GETDATE(),25)+' ******/
     CREATE LOGIN [' + sp.name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + sp.default_database_name + ']'
    END
    FROM   sys.sql_logins sl
           RIGHT JOIN sys.server_principals sp
             ON sl.sid = sp.sid
           JOIN sys.syslogins s
             ON s.sid = sp.sid
    WHERE  sp.TYPE IN ('S','G','U')
           AND sp.name = @loginame
    END

  /*结果集输出*/
  IF @filepath IS NULL
    BEGIN
      SELECT   line
      FROM     ##scriptall
      ORDER BY id ASC
      DROP TABLE ##scriptall
    END
  ELSE
    BEGIN
      declare @cmd varchar(1000)
      SELECT @cmd = 'master..xp_cmdshell ''bcp "select line from  ##ScriptAll order by id" queryout ' + @filepath + ' -T -c -S' + @@SERVERNAME + ''''
      EXEC( @cmd)
      DROP TABLE ##scriptall
    END