By: Tim Ford | Updated: 2010-08-24 | Comments | Related: > SQL Server Agent
Problem
I'm leaving my job as the primary DBA after 10 years. I have a support DBA that will be taking over the primary role. SQL Server creates objects and assigns ownership rights in many cases under the context of the current login. That assuredly means there are SQL Agent jobs owned by my login across all the servers I administer and I need to identify (and more importantly change) them before I depart and my Active Directory login is removed. I support 80+ SQL Server instances and am dreading having to do this. Is there an easier way than connecting to each instance in Object Explorer and examining each job for ownership? Read on to find out how.
Solution
Without a doubt there is an easier process! It involves using your registered servers and executing a query across all servers in your environment. This can be accomplished by using a Central Management Server in SQL Server 2008, but it can also be implemented without the need for a CMS server by simply creating a Local Server Group in SQL Server Management Studio. Once you have a Local Server Group in Registerd Servers Explorer you can execute a query across the entire group of SQL instances in the group - granted that the instance is online and the current security context has appropriate rights to each SQL Server instance.
I have a number of registered server groups partitioned for SQL Server version (2000, 2005 and 2008) as well as local server groups for Test vs. Prod, Application groupings, and groups by cluster. I also have a Registered Local Server Group that contains all my managed SQL Server instances. It is this group that I'll run the query that follows against.
Run the following query against all registerd instances, replacing the templated parameters with the appropriate values for the login you intend to replace and the login that will be the replacement. If you need a primer on templates in SQL Server then one is available here.
SELECT SL.name, '|', SJ.[name], SJ.[job_id], SJ.[owner_sid] AS old_owner_sid
FROM msdb..sysjobs SJ
INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]
WHERE SL.[isntname] = 1 AND SJ.enabled = 1
AND SL.[name] = '
SELECT @new_owner_sid = [sid] FROM MASTER..syslogins WHERE [name] = '
WITH jobs_to_change (old_owner_name, job_id, name, old_owner_sid) AS
(
SELECT SL.name, SJ.[job_id], SJ.[name], SJ.[owner_sid] AS old_owner_sid
FROM msdb..sysjobs SJ
INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]
WHERE SL.[isntname] = 1
AND (SL.[name] = '
UPDATE msdb..sysjobs
SET owner_sid = @new_owner_sid
FROM msdb..sysjobs SJ INNER JOIN jobs_to_change JTC ON SJ.job_id = JTC.job_id;
SELECT SL.name, '|', SJ.[name], SJ.[job_id], SJ.[owner_sid] AS old_owner_sid
FROM msdb..sysjobs SJ
INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]
WHERE SL.[isntname] = 1 AND SJ.enabled = 1
AND SL.[name] = '
The query can be broken down into four sections.
- The first command will list any SQL instances and associated jobs that are listed under the old login's ownership.
- The next statement grouping declares a variable (and assigns a value) for the sid associated with the login you intend to replace the old ownership. The script then goes on to create a Common Table Expression for all jobs that are owned by the old login.
- The next statement updates those affected jobs for each instance with the sid that corresponds to the new login on each instance.
- Finally, the last SELECT statement runs to show that all affected jobs have been updated with the new owner's login.
This process runs extremely fast and runs for each SQL instance in the Local Server Group it is executed against (with the caveats previously mentioned.) It saves you the time of connecting individually to each SQL isntance to identify and update the ownership via the GUI or via a script for each instance. It's a true timesaver and it sounds as though your time is quite limited.
Next Steps
- Learn about using CMS servers to execute a query across multiple SQL Server instances.
- Learn how to register SQL Server instances in SQL Server Management Studio
- Learn more about templates
- The author blogs about SQL Server topics at his personal blog: thesqlagentman.com.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-08-24
