• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Determine the SQL Server Agent user id

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]
FROM dbo.SYSOPERATORS;
GO

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 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
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)
BEGIN
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

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Update_Central_Info', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@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

0
Jim P.
Asked:
Jim P.
  • 2
1 Solution
 
Jim P.Author Commented:
Found it:

USE master
GO
EXEC xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Services\MSSQL$InstanceName','ObjectName'
GO
0
 
Jim P.Author Commented:
Further clarification:
USE [msdb]

DECLARE @Operator As NVarchar(50);
DECLARE @ServerOwner As NVarchar(50);

SELECT TOP 1 @Operator = [Name]
FROM dbo.SYSOPERATORS;

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Services\MSSQL$Instance,'ObjectName', @ServerOwner OUTPUT;

print @ServerOwner

Open in new window

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now