首页 > 代码库 > 分享一个批量导出当前实例下的所有linkedserver脚本

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

分享一个批量导出当前实例下的所有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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/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=http://www.mamicode.com/N‘‘‘+CAST(@connecttimeout AS NVARCHAR(200))+‘‘‘‘        PRINT GO        PRINT EXEC master.dbo.sp_serveroption @server=N‘‘‘+@servername+‘‘‘, @optname=N‘‘lazy schema validation‘‘, @optvalue=http://www.mamicode.com/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=http://www.mamicode.com/N‘‘‘+CAST(@querytimeout AS NVARCHAR(200))+‘‘‘‘        PRINT GO        PRINT EXEC master.dbo.sp_serveroption @server=N‘‘‘+@servername+‘‘‘, @optname=N‘‘use remote collation‘‘, @optvalue=http://www.mamicode.com/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=http://www.mamicode.com/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 = 0xSELECT @i = 1SELECT @length = DATALENGTH (@binvalue)SELECT @hexstring = 0123456789ABCDEFWHILE (@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 <> saELSE  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 哈哈

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