Buck the SQL Dude

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: -- ...

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 ...

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...