分享一个批量导出当前实例下的所有linkedserver脚本

很多时候，我们都需要导出实例下面的登录用户，job，linkedserver等等

导出job比较复杂，下午写了一个脚本把所有的linkedserver导出来，但是密码不会显示出来

下面脚本在SQL2008 R2下面测试通过

-- =============================================-- Author:      <桦仔>-- Blog:        <http://www.cnblogs.com/lyhabc/>-- Create date: <2014/11/3>-- Description: <批量导出实例下所有linkedserver>-- LINK: http://msdn.microsoft.com/zh-cn/library/ms189811.aspx-- =============================================SET NOCOUNT ON USE [master]GO DECLARE @servername NVARCHAR(2000) DECLARE @id INT  DECLARE @scriptdate NVARCHAR(200) DECLARE @productName NVARCHAR(2000)  DECLARE @datasource NVARCHAR(4000)  DECLARE @useself BIT   DECLARE @dist BIT DECLARE @remoteuser NVARCHAR(2000)  DECLARE @collationcompatible BIT  DECLARE @dataaccess BIT  DECLARE @sub BIT DECLARE @pub BIT  DECLARE @rpc BIT  DECLARE @rpcout BIT  DECLARE @connecttimeout BIGINT  DECLARE @lazyschemavalidation BIT  DECLARE @querytimeout BIGINT  DECLARE @useremotecollation BIT  DECLARE @remoteproctransactionpromotion BIT DECLARE LinkserverNameCur CURSORFOR    SELECT  srv.name AS [Name] ,            CAST(srv.server_id AS INT) AS [ID]    FROM    sys.servers AS srv    WHERE   ( srv.server_id != 0 )OPEN LinkserverNameCurFETCH NEXT FROM LinkserverNameCur INTO @servername, @idWHILE @@FETCH_STATUS = 0    BEGIN         SELECT @servername = srv.name ,        @datasource = ISNULL(srv.data_source, N'''') ,        @productName = srv.product ,        @collationcompatible = CAST(srv.is_collation_compatible AS BIT) ,        @dataaccess = CAST(srv.is_data_access_enabled AS BIT) ,        @dist = CAST(srv.is_distributor AS BIT) ,        @pub = CAST(srv.is_publisher AS BIT) ,        @rpc = CAST(srv.is_remote_login_enabled AS BIT) ,        @rpcout = CAST(srv.is_rpc_out_enabled AS BIT) ,        @sub = CAST(srv.is_subscriber AS BIT) ,        @connecttimeout = srv.connect_timeout ,        @lazyschemavalidation = srv.lazy_schema_validation ,        @querytimeout = srv.query_timeout ,        @useremotecollation = srv.uses_remote_collation ,        @remoteproctransactionpromotion = CAST(srv.is_remote_proc_transaction_promotion_enabled AS BIT) FROM   sys.servers AS srv WHERE  ( srv.server_id != 0 )        AND ( srv.name = @servername )        AND ( srv.[server_id] = @id ) SELECT @remoteuser = ISNULL(ll.remote_name, N'') ,        @useself = CAST(ll.uses_self_credential AS BIT) FROM   sys.servers AS srv        INNER JOIN sys.linked_logins ll ON ll.server_id = CAST(srv.server_id AS INT)        LEFT OUTER JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id WHERE  ( ( srv.server_id != 0 )          AND ( srv.name = @servername)        )        IF (@servername IS NOT NULL AND @id IS NOT NULL)        BEGIN         SELECT  @scriptdate=CONVERT(NVARCHAR(200),GETDATE(),120)        PRINT '/*************************************SCRIPT FOR LINKED SERVER: ['+@servername+']****************************************************/'        PRINT '/********************************************************************************************************************************/'        PRINT 'USE [master]'+CHAR(13)+'GO'        PRINT '/****** Object:  LinkedServer ['+@servername+']    Script Date: '+@scriptdate+' ******/'        PRINT 'EXEC master.dbo.sp_addlinkedserver @server = N'''+@servername+''', @srvproduct=N'''+@productName+''''        PRINT '/* For security reasons the linked server remote logins password is changed with ######## */'        DECLARE @sql NVARCHAR(2000)        IF (@remoteuser IS NOT  NULL AND @remoteuser != N'')        BEGIN         SET @sql='EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+@datasource+''',@useself=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END +''',@locallogin=NULL,@rmtuser=N'''+@remoteuser+''',@rmtpassword=''########'''        END        ELSE        BEGIN         SET @sql='EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+@datasource+''',@useself=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END +''',@locallogin=NULL,@rmtuser=N'''',@rmtpassword=''########'''        END        PRINT @sql        PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''collation compatible'', @optvalue=N'''+CASE @useself WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''data access'', @optvalue=N'''+CASE @dataaccess WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''dist'', @optvalue=N'''+CASE @dist WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''pub'', @optvalue=N'''+CASE @pub WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''rpc'', @optvalue=N'''+CASE @rpc WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''rpc out'', @optvalue=N'''+CASE @rpcout WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''sub'', @optvalue=N'''+CASE @sub WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''connect timeout'', @optvalue=N'''+CAST(@connecttimeout AS NVARCHAR(200))+''''        PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''lazy schema validation'', @optvalue=N'''+CASE @lazyschemavalidation WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''query timeout'', @optvalue=N'''+CAST(@querytimeout AS NVARCHAR(200))+''''        PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''use remote collation'', @optvalue=N'''+CASE @useremotecollation WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT 'EXEC master.dbo.sp_serveroption @server=N'''+@servername+''', @optname=N''remote proc transaction promotion'', @optvalue=N'''+CASE @remoteproctransactionpromotion WHEN 0 THEN 'false' ELSE 'true' END+''''         PRINT 'GO'        PRINT '/********************************************************************************************************************************/'        PRINT '/********************************************************************************************************************************/'        PRINT CHAR(13)        PRINT CHAR(13)        PRINT CHAR(13)        END         FETCH NEXT FROM LinkserverNameCur INTO @servername, @id    ENDCLOSE LinkserverNameCurDEALLOCATE LinkserverNameCur

如果要迁移登录用户这里有一篇文章

如何在 SQL Server 2005 实例之间传输登录和密码

脚本如下:

USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @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_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO

如果脚本有错误的话可以跟我反映

如有不对的地方，欢迎大家拍砖o(∩_∩)o 哈哈