February 18, 2008 at 1:50 pm
Is there a way to retrieve sql server/sql agent startup account without going to the server/host itself?
Some similar stored procedure like “xp_sqlagent_proxy_account” while it is for proxy account. Or use xp_regread? From either query analyzer tool or other client tools.
February 18, 2008 at 2:56 pm
Try this (assumes you have a query window open to the SQL instance in question). It's from a documentation script I found and allows for instances:
[font="Courier New"]-- the value for Start is 2=Automatic, 3=Manual, 4=Disabled
DECLARE @sql nvarchar(4000)
--MSSQLSERVER Service Account
IF SERVERPROPERTY('InstanceName') IS NULL
BEGIN
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','ObjectName'
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\MSSQLSERVER','Start'
END
ELSE
BEGIN
SET @sql = 'master..xp_regread ''HKEY_LOCAL_MACHINE'' ,''SYSTEM\CurrentControlSet\Services\MSSQL$' + LTRIM(CAST(SERVERPROPERTY('InstanceName') AS sysname)) + ''',''ObjectName'''
EXEC (@sql)
SET @sql = 'master..xp_regread ''HKEY_LOCAL_MACHINE'' ,''SYSTEM\CurrentControlSet\Services\MSSQL$' + LTRIM(CAST(SERVERPROPERTY('InstanceName') AS sysname)) + ''',''Start'''
EXEC (@sql)
END
--SQLSERVERAGENT Service Account
IF SERVERPROPERTY('InstanceName') IS NULL
BEGIN
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT','ObjectName'
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE' ,'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT','Start'
END
ELSE
BEGIN
SET @sql = 'master..xp_regread ''HKEY_LOCAL_MACHINE'' ,''SYSTEM\CurrentControlSet\Services\SQLAgent$' + LTRIM(CAST(SERVERPROPERTY('InstanceName') AS sysname)) + ''',''ObjectName'''
EXEC (@sql)
SET @sql = 'master..xp_regread ''HKEY_LOCAL_MACHINE'' ,''SYSTEM\CurrentControlSet\Services\SQLAgent$' + LTRIM(CAST(SERVERPROPERTY('InstanceName') AS sysname)) + ''',''Start'''
EXEC (@sql)
END[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 18, 2008 at 3:22 pm
Great stuff. It works on both SQL2005 and SQL2000.
Thank you Scott!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply