March 27, 2007 at 8:24 am
I have to go through a lot of SQL severs to get the service accounts running each service, it would be a heck of a lot easier if I had a select statement or system stored proc to list the accounts running each service...
Is there such a way to do this via T-SQL?
Thanks,
Leeland
March 27, 2007 at 9:11 am
Not to my knowledge (at least not directly from T-SQL). You may find a good way using SQL-DMO or SQL-SMO to do this but I don't have an example to provide you.
March 27, 2007 at 9:44 am
I didn't think so...I could not find anything about it anywhere...
March 27, 2007 at 11:14 am
Hey Steve Thanks for the code...works like a charm. I altered it a small bit to account for both service accounts and also listing one backup device...
SET NOCOUNT ON
DECLARE @SQLService VARCHAR(60) DECLARE @AgentService VARCHAR(60)
EXEC xp_regread @root_key = 'HKEY_LOCAL_MACHINE', @key = 'SYSTEM\ControlSet001\Services\MSSQLServer', @valuename = 'ObjectName', @value = @SQLService output
EXEC xp_regread @root_key = 'HKEY_LOCAL_MACHINE', @key = 'SYSTEM\ControlSet001\Services\SQLSERVERAGENT', @valuename = 'ObjectName', @value = @AgentService output
SELECT @SQLService AS 'SQL Service Account', @AgentService AS 'SQL Agent Account', (SELECT TOP 1 phyname FROM master..sysdevices WHERE phyname LIKE '\\%') AS 'Backup Device'
Thanks again man,
Leeland
March 28, 2007 at 2:19 pm
For SQL Server 2000 and 2005, here is a SQL batch that inlcudes supports for named instances. It has been tested when SQL Server is running on a cluster and when the SQL Servers that do not used the default collation. It also gets the service account for the DTS and Text search services, which should always be local system (or NT AUTHORITY\NetworkService on Windows 2003), as there is always someone who gets confused and changes these services to use some other account causing the services to fail.
Set nocount on
Set xact_abort on
Declare @registrypath varchar(200)
,@namedinstanceindchar(1)
,@instancenamevarchar(128)
,@sqlserversvcaccountvarchar(128)
,@sqlagentsvcaccountvarchar(128)
,@dtcsvcaccountvarchar(128)
,@sqlsearchsvcaccountvarchar(128)
,@sqlserverstartupvarchar(128)
,@sqlagentstartupvarchar(128)
,@dtcstartupvarchar(128)
,@sqlsearchstartupvarchar(128)
Create table #registryentry(value varchar(50), data varchar(50))
If @@servername is null
Or (charindex('\',@@servername)=0)
set @namedinstanceind = 'n'
Else
Begin
set @namedinstanceind = 'y'
set@instancename = right( @@servername , len(@@servername) - charindex('\',@@servername))
End
--sql server
Set@registrypath = 'system\currentcontrolset\services\'
If@namedinstanceind = 'n'
set@registrypath = @registrypath + 'mssqlserver'
Else
set@registrypath = @registrypath + 'mssql$' + @instancename
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname'
Select @sqlserversvcaccount = data from #registryentry
Delete from #registryentry
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start'
Select @sqlserverstartup = data from #registryentry
Delete from #registryentry
--sql agent
Set@registrypath = 'system\currentcontrolset\services\'
If@namedinstanceind = 'n'
set@registrypath = @registrypath + 'sqlserveragent'
Else
set@registrypath = @registrypath + 'sqlagent$' + @instancename
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname'
Select @sqlagentsvcaccount = data from #registryentry
Delete from #registryentry
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start'
Select @sqlagentstartup = data from #registryentry
Delete from #registryentry
--distributed transaction coordinator
Set@registrypath = 'system\currentcontrolset\services\msdtc'
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname'
Select @dtcsvcaccount = data from #registryentry
Delete from #registryentry
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start'
Select @dtcstartup = data from #registryentry
Delete from #registryentry
--search (sql server )
Set@registrypath = 'system\currentcontrolset\services\mssearch'
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'objectname'
Select @sqlsearchsvcaccount = data from #registryentry
Delete from #registryentry
Insert #registryentry
Exec master..xp_regread 'hkey_local_machine' , @registrypath,'start'
Select @sqlsearchstartup = data from #registryentry
Delete from #registryentry
Selectcast( serverproperty ('machinename') as nvarchar(128) )as machinename
,coalesce ( cast( serverproperty ('instancename')as nvarchar(128) ) , 'default')as instancename
,@sqlserversvcaccountas sqlserversvcaccount
,@sqlagentsvcaccountas sqlagentsvcaccount
,@dtcsvcaccountas dtcsvcaccount
,@sqlsearchsvcaccountas sqlsearchsvcaccount
,@sqlserverstartupas sqlserverstartup
,@sqlagentstartupas sqlagentstartup
,@dtcstartupas dtcstartup
,@sqlsearchstartupas sqlsearchstartup
SQL = Scarcely Qualifies as a Language
April 13, 2012 at 8:45 am
Great script. Thank you.
Dan
October 5, 2012 at 10:13 am
For SQL Server 2008 R2 and later you could take advantage of DMV's and use a script like the one below:
USE [master]
GO
SELECT value_data as [AccountName], registry_key
FROM sys.dm_server_registry
WHERE
registry_key LIKE '%MSSQLSERVER%' --For named instance change to MSSQL$<instanceName>
AND value_name = 'ObjectName'
UNION
SELECT value_data as [AccountName], registry_key
FROM sys.dm_server_registry
WHERE
registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
AND value_name = 'ObjectName'
April 3, 2014 at 8:14 am
Leeland and b.henry,
Thanks. Both of those queries worked for me. Very much appreciated. I'm so glad I found this thread.
Best Regards
April 3, 2014 at 8:17 am
If you are looking for 2008R2 and later, you can query a dmv for this info (throwing it out there due to the recent activity on this thread).
SELECT *
FROM sys.dm_server_services;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 8, 2015 at 3:03 pm
Most excellent add to thread. The 2008 dm_server_services lists all services and works on 2012 as well.
February 16, 2016 at 4:34 am
Is there a an extra dmv I can add to sys.dm_server_services so I can use a query to highlight when an account is not a common domain account ? and also check for an associated logon
If not would something like this work ?
select servicename,service_account ,
case
when service_account like '%LocalService%' then 'Local Service Account'
when service_account like '%localsystem%' then 'Local System Account'
when service_account like '%NT Service%' then 'NT Network Account'
when service_account like '%NetworkService%' then 'NT Network Account'
when service_account like '%Administrator%' then 'Local Admin Account'
else 'Common Domain Account'
End as 'Account Type',
case when b.name is null then 'No Login'
else 'Login Exists'
end as 'Login Status'
from sys.dm_server_services a
left join syslogins b
on a.service_account = b.name
thanks
~simon 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply