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 的新建脚本，将脚本存放到指定文件下。
1 CREATE PROC [dbo].[spm_GetLoginDetails] 2 @loginame varchar(100)=null, 3 @filepath varchar(1000)=null 4 AS 5 SET NOCOUNT ON 6 7 DECLARE @bin_pwd VARBINARY (8000) 8 DECLARE @bin_sid VARBINARY (8000)--VARBINARY密码和sid 9 DECLARE @string_pwd VARCHAR(8000) 10 SET @string_pwd='' 11 DECLARE @string_sid VARCHAR(8000) 12 SET @string_sid='' 13 14 DECLARE @pwd INT,@sid INT 15 /*脚本存放*/ 16 CREATE TABLE ##scriptall(id INT IDENTITY(1,1),line VARCHAR(MAX)) 17 18 /*如果loginame 不存在则输出所有脚本*/ 19 IF @loginame IS NULL 20 BEGIN 21 CREATE TABLE #temp(id INT IDENTITY(1,1),name VARCHAR(100),sid VARBINARY(256),string_pwd VARCHAR(1000),string_sid VARCHAR(1000)) 22 23 INSERT INTO #temp(sid,name) 24 SELECT sid,name FROM sys.sql_logins 25 26 DECLARE @minid INT,@maxid INT 27 SELECT @minid=min(id),@maxid=MAX(id) FROM #temp 28 29 /*VARBINARY类型转化处理*/ 30 WHILE (@minid<=@maxid) 31 BEGIN 32 SELECT @bin_pwd = sl.password_hash,@bin_sid=sl.sid,@string_pwd='',@string_sid='' 33 FROM sys.sql_logins sl join #temp t on t.sid =sl.sid 34 where t.id=@minid 35 36 /*密码类型转化*/ 37 SELECT @pwd = Datalength(@bin_pwd),@sid=Datalength(@bin_sid) 38 WHILE @pwd > 0 39 BEGIN 40 SELECT @string_pwd = Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1) / 16 + 1,1) + 41 Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1)%16 + 1,1) + @string_pwd 42 ,@pwd = @pwd - 1 43 END 44 SELECT @string_pwd = ('0x' + @string_pwd) 45 46 /*sid类型转化*/ 47 WHILE @sid > 0 48 BEGIN 49 SELECT @string_sid = Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1) / 16 + 1,1) + 50 Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1)%16 + 1,1) + @string_sid, 51 @sid = @sid - 1 52 END 53 SELECT @string_sid = ('0x' + @string_sid) 54 UPDATE #temp SET string_pwd=@string_pwd,string_sid=@string_sid WHERE id=@minid 55 SET @minid=@minid+1 56 END 57 /*导出login新建脚本*/ 58 INSERT INTO ##scriptall(line) 59 SELECT CASE WHEN sp.TYPE = 'S' THEN ' 60 /****** Object: Login ['+sp.name+'] Script Date: '+convert(VARCHAR,GETDATE(),25)+' ******/ 61 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 62 ELSE 63 '/****** Object: Login ['+sp.name+'] Script Date: '+convert(VARCHAR,GETDATE(),25)+' ******/ 64 CREATE LOGIN [' + sp.name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + sp.default_database_name + ']' 65 END 66 FROM sys.sql_logins sl 67 RIGHT JOIN sys.server_principals sp 68 ON sl.sid = sp.sid 69 JOIN sys.syslogins s 70 ON s.sid = sp.sid 71 left join #temp t on t.sid=sl.sid 72 WHERE sp.TYPE IN ('S','G','U') and loginname<>'sa' 73 ORDER BY sp.name 74 END 75 /*若存在loginame，则输出相应的脚本*/ 76 ELSE 77 BEGIN 78 79 IF EXISTS (SELECT TOP 1 1 FROM sys.sql_logins s WHERE s.name = @loginame) 80 BEGIN 81 82 SELECT @bin_pwd = sl.password_hash,@bin_sid=sl.sid 83 FROM sys.sql_logins sl 84 WHERE sl.name = @loginame 85 SELECT @pwd = Datalength(@bin_pwd),@sid=Datalength(@bin_sid) 86 /*密码类型转化*/ 87 WHILE @pwd > 0 88 BEGIN 89 SELECT @string_pwd = Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1) / 16 + 1, 90 1) + Substring('0123456789ABCDEF',Substring(@bin_pwd,@pwd,1)%16 + 1, 91 1) + @string_pwd,@pwd = @pwd - 1 92 END 93 SELECT @string_pwd = ('0x' + @string_pwd) 94 /*SID类型转化*/ 95 WHILE @sid > 0 96 BEGIN 97 SELECT @string_sid = Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1) / 16 + 1, 98 1) + Substring('0123456789ABCDEF',Substring(@bin_sid,@sid,1)%16 + 1, 99 1) + @string_sid,@sid = @sid - 1 100 101 END 102 SELECT @string_sid = ('0x' + @string_sid) 103 104 END 105 /*导出login新建脚本*/ 106 INSERT INTO ##scriptall(line) 107 SELECT CASE WHEN sp.TYPE = 'S' THEN ' 108 /****** Object: Login ['+sp.name+'] Script Date: '+convert(varchar,GETDATE(),25)+' ******/ 109 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 110 ELSE 111 '/****** Object: Login ['+sp.name+'] Script Date: '+convert(varchar,GETDATE(),25)+' ******/ 112 CREATE LOGIN [' + sp.name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + sp.default_database_name + ']' 113 END 114 FROM sys.sql_logins sl 115 RIGHT JOIN sys.server_principals sp 116 ON sl.sid = sp.sid 117 JOIN sys.syslogins s 118 ON s.sid = sp.sid 119 WHERE sp.TYPE IN ('S','G','U') 120 AND sp.name = @loginame 121 END 122 123 /*结果集输出*/ 124 IF @filepath IS NULL 125 BEGIN 126 SELECT line 127 FROM ##scriptall 128 ORDER BY id ASC 129 DROP TABLE ##scriptall 130 END 131 ELSE 132 BEGIN 133 declare @cmd varchar(1000) 134 SELECT @cmd = 'master..xp_cmdshell ''bcp "select line from ##ScriptAll order by id" queryout ' + @filepath + ' -T -c -S' + @@SERVERNAME + '''' 135 EXEC( @cmd) 136 DROP TABLE ##scriptall 137 END
