SQLserver先迁移数据库，再迁移登录账号

  1 USE master
  2 GO
  3 IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4   DROP PROCEDURE sp_hexadecimal
  5 GO
  6 CREATE PROCEDURE sp_hexadecimal
  7     @binvalue varbinary(256),
  8     @hexvalue varchar (514) OUTPUT
  9 AS
 10 DECLARE @charvalue varchar (514)
 11 DECLARE @i int
 12 DECLARE @length int
 13 DECLARE @hexstring char(16)
 14 SELECT @charvalue = '0x'
 15 SELECT @i = 1
 16 SELECT @length = DATALENGTH (@binvalue)
 17 SELECT @hexstring = '0123456789ABCDEF'
 18 WHILE (@i <= @length)
 19 BEGIN
 20   DECLARE @tempint int
 21   DECLARE @firstint int
 22   DECLARE @secondint int
 23   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 24   SELECT @firstint = FLOOR(@tempint/16)
 25   SELECT @secondint = @tempint - (@firstint*16)
 26   SELECT @charvalue = @charvalue +
 27     SUBSTRING(@hexstring, @firstint+1, 1) +
 28     SUBSTRING(@hexstring, @secondint+1, 1)
 29   SELECT @i = @i + 1
 30 END
 31  
 32 SELECT @hexvalue = @charvalue
 33 GO
 34  
 35 IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
 36   DROP PROCEDURE sp_help_revlogin
 37 GO
 38 CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
 39 DECLARE @name sysname
 40 DECLARE @type varchar (1)
 41 DECLARE @hasaccess int
 42 DECLARE @denylogin int
 43 DECLARE @is_disabled int
 44 DECLARE @PWD_varbinary  varbinary (256)
 45 DECLARE @PWD_string  varchar (514)
 46 DECLARE @SID_varbinary varbinary (85)
 47 DECLARE @SID_string varchar (514)
 48 DECLARE @tmpstr  varchar (1024)
 49 DECLARE @is_policy_checked varchar (3)
 50 DECLARE @is_expiration_checked varchar (3)
 51  
 52 DECLARE @defaultdb sysname
 53  
 54 IF (@login_name IS NULL)
 55   DECLARE login_curs CURSOR FOR
 56  
 57       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
 58 sys.server_principals p LEFT JOIN sys.syslogins l
 59       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
 60 ELSE
 61   DECLARE login_curs CURSOR FOR
 62  
 63  
 64       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
 65 sys.server_principals p LEFT JOIN sys.syslogins l
 66       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
 67 OPEN login_curs
 68  
 69 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
 70 IF (@@fetch_status = -1)
 71 BEGIN
 72   PRINT 'No login(s) found.'
 73   CLOSE login_curs
 74   DEALLOCATE login_curs
 75   RETURN -1
 76 END
 77 SET @tmpstr = '/* sp_help_revlogin script '
 78 PRINT @tmpstr
 79 SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 80 PRINT @tmpstr
 81 PRINT ''
 82 WHILE (@@fetch_status <> -1)
 83 BEGIN
 84   IF (@@fetch_status <> -2)
 85   BEGIN
 86     PRINT ''
 87     SET @tmpstr = '-- Login: ' + @name
 88     PRINT @tmpstr
 89     IF (@type IN ( 'G', 'U'))
 90     BEGIN -- NT authenticated account/group
 91  
 92       SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
 93     END
 94     ELSE BEGIN -- SQL Server authentication
 95         -- obtain password and sid
 96             SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
 97         EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
 98         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 99  
100         -- obtain password policy state
101         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
102         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
103  
104             SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
105  
106         IF ( @is_policy_checked IS NOT NULL )
107         BEGIN
108           SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
109         END
110         IF ( @is_expiration_checked IS NOT NULL )
111         BEGIN
112           SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
113         END
114     END
115     IF (@denylogin = 1)
116     BEGIN -- login is denied access
117       SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
118     END
119     ELSE IF (@hasaccess = 0)
120     BEGIN -- login exists but does not have access
121       SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
122     END
123     IF (@is_disabled = 1)
124     BEGIN -- login is disabled
125       SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
126     END
127     PRINT @tmpstr
128   END
129  
130   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
131    END
132 CLOSE login_curs
133 DEALLOCATE login_curs
134 RETURN 0
135 GO

EXEC sp_help_revlogin