迁移MSSQL实例的所有login（包含密码）

迁移数据库的时候肯定会涉及到login的迁移（包含数据库除外）。

而一般我们迁移login的时候，可能会使用在某个login上右键生成脚本这样的做法。但是这样生成的脚本不能把密码也生成出来。

而且你只能一个一个login的弄，如果有大量login的话，这样会非常没有效率。其实微软提供了login迁移的脚本。

以下纯属摘抄自KB918992：

创建两个存储过程：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135

USE master 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 SET @tmpstr = '/* sp_help_revlogin 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 -- 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                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 -- 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

 运行存储过程会生成login的迁移script：

?

1	EXEC sp_help_revlogin