Determine the SQL Server Agent user id

Posted on 2009-12-17
Last Modified: 2012-05-08
I'm trying to setup a bunch of jobs on varoius SQL Servers across multiple domains. I want to be able to use the same script just picking the SQL Server Agent's user ID as the job owner.

Where is it stored in SQL Server (or on the system) so that I can dynamically use it without having to manually change the script each time?  It should be stored in some table or a consistent spot in the registry if I have to do an "EXEC xp_regread".

I can get the sysoperator easily. I'm just getting beat on the SQL Server Agent.
USE [msdb]

DECLARE @Operator As NVarchar(50);

DECLARE @ServerOwner As NVarchar(50);

SELECT TOP 1 @Operator = [Name]



SELECT Top 1 @ServerOwner = <SQL Server Agent ID> FROM WHEREVER (or even an an EXEC xp_regread)

/****** Object:  Job [Update_Central_Info]    Script Date: 12/17/2009 09:52:14 ******/



SELECT @ReturnCode = 0

/****** Object:  JobCategory [DBA Maintenance]    Script Date: 12/17/2009 09:52:14 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Maintenance' AND category_class=1)


EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback



EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Update_Central_Info', 







		@description=N'Write server info to a central server.', 

		@category_name=N'DBA Maintenance', 

		@owner_login_name=@ServerOwner , 

		@notify_email_operator_name=@Operator, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Open in new window

Question by:Jim P.
    LVL 38

    Accepted Solution

    Found it:

    USE master
    EXEC xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Services\MSSQL$InstanceName','ObjectName'
    LVL 38

    Author Comment

    by:Jim P.
    Further clarification:
    USE [msdb]
    DECLARE @Operator As NVarchar(50);
    DECLARE @ServerOwner As NVarchar(50);
    SELECT TOP 1 @Operator = [Name]
    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Services\MSSQL$Instance,'ObjectName', @ServerOwner OUTPUT;
    print @ServerOwner

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now