read the name of the sql server agent account

As time goes on and the push to make things more dynamic continues, I find myself needing to create procedures and DTS/SSIS packs more intelligent about the environment to which it is being deployed. This is especially true now with the upgrade from earlier versions of SQL Server to SQL 2005 and soon SQL 2008.

This little script is what I use to determine the name of the sql server agent account so that I can correctly set the @owner variable of the sp_add_job procedure.

One could concievably put this into a stored procedure, if one so desired:)

Enjoy:


-- script to read the name of the sql server agent account
/**********************************************************************
-- Declare your variables...
***********************************************************************/
DECLARE @rc int,
@machine nvarchar(128),
@instance nvarchar(128),
@profile nvarchar(128),
@regpath nvarchar(256),
@account nvarchar(128)

IF (charindex(N'7.00', @@version, 0) > 0)
BEGIN
SELECT @instance = NULL
END
IF (charindex(N'8.00', @@version, 0) > 0) or (charindex(N'9.00', @@version, 0) > 0)
BEGIN
SELECT @machine = CONVERT(nvarchar(128), serverproperty('machine')),
@instance = CONVERT(nvarchar(128),serverproperty('instance'))
END
/**********************************************************************
-- Check if not running SQL Server as LocalSystem
-- @rc 0 = LocalSystem, 1 = running under account
***********************************************************************/
EXEC @rc = master.dbo.xp_MSLocalSystem @machine, @instance
IF (@rc = 0)
BEGIN
PRINT 'SQL Server is running under the LocalSystem account'
RETURN
END
/**********************************************************************
-- Get account name running SQL Server
**********************************************************************/
IF (@instance IS NULL)
BEGIN
SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
END
ELSE
BEGIN
SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT$' + @instance
END
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
@regpath,
N'ObjectName',
@account OUTPUT,
N'no_output'
PRINT 'SQL Server is running under account ' + @account
RETURN
GO