dbo.usp_dba_createlinkedserver.sql

USE [NBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
    Procedure Name: dbo.usp_dba_createlinkedserver
    Purpose: Create and configure a linked server entry for SQL Server instances
+
+
+    Project: T-SQL-Scripts
+
+
+    Date Created: 2025-10-15
+
+
+    Author: Ludwig Mark
+
+
+    Parameters: see signature (@linkedservername, @linkedserverip, username/password)
+
+
+    Returns: None
+
+
+    Notes:
+
+    - Uses dynamic SQL to call sp_addlinkedserver and sp_addlinkedsrvlogin
+
+
+    Dependencies:
+
+    - Requires appropriate sysadmin or server-level rights to create linked servers
+
+
+    Security:
+
+    - Avoid placing credentials in scripts; restrict access to this procedure
+
+
+    LICENSE: MIT
+
+------------------------------------------------------------------------------*/

CREATE   PROCEDURE [dbo].[usp_dba_createlinkedserver] 
    @linkedservername NVARCHAR(128)
    , @linkedserverip NVARCHAR(128)
    , @linkedserverusername NVARCHAR(128)
    , @linkedserverpassword NVARCHAR(128)
    AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @addlinkedserver NVARCHAR(max) = '
        DECLARE master.dbo.sp_addlinkedserver 
            @server = '''+@linkedservername+''', 
            @srvproduct=''sqlserver'', 
            @provider=''SQLNCLI'', 
            @datasrc='''+@linkedserverip+'''

        DECLARE master.dbo.sp_addlinkedsrvlogin 
            @rmtsrvname='''+@linkedservername+''', 
            @useself=''false'', @locallogin=null, 
            @rmtuser='''+@linkedserverusername+''', 
            @rmtpassword='''+@linkedserverpassword+'''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''collation compatible'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''data access'', 
            @optvalue=''true''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''dist'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption     
            @server=N'''+@linkedservername+''', 
            @optname=''pub'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''rpc'', 
            @optvalue=''true''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''rpc out'', 
            @optvalue=''true''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''sub'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''connect timeout'', 
            @optvalue=''0''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''collation name'', 
            @optvalue=null

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''lazy schema validation'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''query timeout'', 
            @optvalue=''0''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''use remote collation'', 
            @optvalue=''false''

        DECLARE master.dbo.sp_serveroption 
            @server=N'''+@linkedservername+''', 
            @optname=''remote proc transaction promotion'', 
            @optvalue=''true''
    
    '

    DECLARE @addlinkedfailure NVARCHAR(max) = 
    '***********************' +
    'Server Creation Failed!' + char(10) + 
    '***********************'

    DECLARE @addlinkedsuccess NVARCHAR(max) = 
    '****************************' +
    'Server Created Successfully!' + char(10) + 
    '****************************' +
    'Try: "select * from '+QUOTENAME(@linkedservername)+'.master.sys.servers"'

    DECLARE @addlinkedtest NVARCHAR(max) = '
        select  * 
        into    #temp 
        from    '+QUOTENAME(@linkedservername)+'.master.sys.servers
        drop    table #temp
    '

    DECLARE @addlinkeddrop nvarchar(max) = '
        DECLARE master.dbo.sp_dropserver 
            @server='''+@linkedservername+''', 
            @droplogins=''droplogins''
    '
    BEGIN TRY
	    EXEC (@addlinkedserver);
	    BEGIN TRY
		    EXEC (@addlinkedtest);
		    PRINT @addlinkedsuccess;
	    END TRY
	    BEGIN CATCH
		    BEGIN TRY
			    EXEC (@addlinkeddrop);
			    PRINT @addlinkedfailure
		    END TRY
		    BEGIN CATCH
			    PRINT @addlinkedfailure
		    END CATCH
	    END CATCH
    END TRY
    BEGIN CATCH
	    BEGIN TRY
		    EXEC (@addlinkeddrop);
		    PRINT @addlinkedfailure
	    END TRY
	    BEGIN CATCH
		    PRINT @addlinkedfailure
	    END CATCH
    END CATCH

END
GO