SP to Cycle thru All Linked Servers

We have a central DB server for the DBAs to query other SQL servers from.
It has linked servers to the various servers that allow us to centralize some processes.

Today, I am going to post the basic template I use for cycling thru those servers.
This template happens to pull back all of the SQL Job Owners for the various SQL jobs.
We do this to prevent PROD jobs from running under a users login.
If the user leaves, the jobs will fail and that has caused issues before.

Obviously you can change out that the dynamic SQL does but this works well as a basic template to query against all of the other servers to get data and load it back into a single central table to manage the data. 


/****** Object:  StoredProcedure [security].[templateCycleAllLinkedServers] ******/
-- =============================================
-- Author:
-- Create date: 20210526
-- Description: Template for process to cycle thru all the servers to do something
-- Calling App:
-- Ticket Info:
--
-- =============================================
ALTER PROCEDURE [security].[templateCycleAllLinkedServers]
AS
-- =============================================
-- Change History:
-- Name LAN ID DATE Tix# Purpose
-- Example Name EXANAME 20090512 1234 User asked for new column (Cause)
--
-- =============================================
BEGIN /* PROCEDURE */
SET NOCOUNT ON
/* DDL CONTENT START */
-- VARIABLES
DECLARE
@sql NVARCHAR(2500)
, @ServerListID INT
, @ServerLoops INT
, @ServerName VARCHAR(255)

-- SET INITIAL VALUES
SELECT @sql = ''
SELECT @ServerListID = 1
SELECT @ServerLoops = 1


-- CLEAN WORKING TABLES
TRUNCATE TABLE [dbo].[ServerList]
TRUNCATE TABLE [dbo].[SQLJobOwners]


-- LOAD CURRENT LINKED SERVERS
INSERT INTO [dbo].[ServerList]
(
    [ServerName]
)
SELECT
[ss].[srvname] 
FROM
[sys].[sysservers] AS [ss] WITH (NOLOCK)
ORDER BY
[ss].[srvname]

-- SET NUMBER OF SERVERS TO CHECK
SELECT
@ServerLoops = MAX([sl].[ServerListID])
FROM
[dbo].[ServerList] AS [sl] WITH (NOLOCK)
/* DDL CONTENT END */


/* DML CONTENT START */
WHILE @ServerListID <= @ServerLoops
BEGIN /* WHILE */
SELECT @ServerName = [sl].[ServerName]
FROM
[dbo].[ServerList] AS [sl] WITH (NOLOCK)
WHERE
[sl].[ServerListID] = @ServerListID
-- template of SQL to do an insert into a table while selecting from another server
SELECT
@sql = @sql + '
INSERT INTO [dbo].[SQLJobOwners] (    [ServerName]  , [JobName]  , [JobOwner])
SELECT
''' + @ServerName + ''' AS [ServerName]
, [sj].[name] AS [JobName]
, SUSER_NAME([sj].[owner_sid]) AS [JobOwner]
FROM
[' + @ServerName + '].[msdb].[dbo].[sysjobs] AS [sj] WITH (NOLOCK)
ORDER BY
[sj].[name]
'

-- PUT IN TRY CATCH TO RUN SQL
BEGIN TRY
PRINT @ServerName
EXECUTE [sys].[sp_executesql] @sql
END TRY
BEGIN CATCH
SELECT  
ERROR_NUMBER() AS ErrorNumber  
,ERROR_SEVERITY() AS ErrorSeverity  
,ERROR_STATE() AS ErrorState  
,ERROR_PROCEDURE() AS ErrorProcedure  
,ERROR_LINE() AS ErrorLine  
,ERROR_MESSAGE() AS ErrorMessage  
, @ServerName
END CATCH

SELECT @sql = ''
SELECT @ServerListID = @ServerListID + 1

END /* WHILE */
/* DML CONTENT END */

SET NOCOUNT OFF
END /* PROCEDURE */



#MicrosoftSQL
#Logins
#DynamicSQL
#BuckTheSQLDude


Comments

Post a Comment

Popular posts from this blog

Recreating Permissions from 1 server to another for a different group

The second script I am going to post is used to copy all the permissions in all databases for a login matching the pattern and generating the GRANT permissions for a different login. You could swap out the REPLACE line with just the [dbprinc].[name] if you want the same group to have them assigned on a different server. I used this to migrate permissions to both DEV and TEST for 3 servers. All told, it generated about 19,000 rows between them. Imagine having to copy those manually.  /***********************************************/ /*************       START CODE       ***************/ /***********************************************/ DECLARE @DBName VARCHAR (500) , @sqltext NVARCHAR(MAX) SELECT @DBName = '' SELECT @sqltext = '' IF OBJECT_ID('[tempdb]..#Perms') IS NOT NULL BEGIN DROP TABLE #Perms END  CREATE TABLE #Perms ( [PermString] VARCHAR(MAX) ) DECLARE DBNames_Cursor CURSOR ...
Read more

Recreating Logins From 1 Server to Apply to Another

A little background on this script. We had to build out a new TEST environment along with a new security schema. We had to apply it to PROD first due to issues and then migrate it down. With nearly 500 DBs on multiple servers, I didn't want to have to try to redo all of the permissions to each object manually. I build this script to cycle thru all of the logins on the server that fit a particular AD pattern and generate the CREATE script for them with the TEST name instead.  Once the script was run, all I had to do was copy it, paste it in the TEST environment, and run it.  It automatically adds the logins to each DB not just to the server itself. Each environment has an AD group for the DB in a standard pattern that is specific to that environment. The pattern is something like this: PROD - PRD_ DBName _Read or PRD_ DBNam e_Edit (depends on level of permissions) TEST - TST_ DBName _Read or TST_ DBName _Edit (depends on level of permissions) DEV - DEV_ DBName _Read or DEV_ DBN...
Read more