There's an excellent tip about SQL Server Services, Checking SQL Service Status - An Evolution Part 1, by Robert Pearl. Indeed, this a helpful article, but a very critical piece was missing from it: Which user runs the service. I needed to check which account was associated with the SQL Services very quickly. Not only is the DBA responsible for the running status of the Services, they are responsible that the Service is running with a proper owner.
Although with SQL Server 2012 a new DMV is used to find the status of the services, sys.dm_server_services: You will be able to query the ServiceName, Startup_Type, Startup_Type_Desc, Status, Status_Desc,Process_ID, and Last_Startup_Time.
However, the code below will give us more detail information for each service(s). There's more flexibility with service control manager, SC, since you can use different options that you may require.
We will use the xp_cmdshell and sc.exe for the code below and we will pass the qc option, which queries the configuration information for the service. You can also use the queryex option, which queries the extended status for a service, this option will give you the Process_ID if needed.
Here are all the options for the SC command:
DESCRIPTION: SC is a command line program used for communicating with the Service Control Manager and services.
T-SQL Script to Check the SQL Server Services Owner
Below is a script that calls xp_cmdshell and the sc.exe application to check the status for the SQL Server services:
SET NOCOUNT ON
-- Temporary Tables
CREATE TABLE #tmpServices
CREATE TABLE #tmpServicesDetail
CREATE TABLE #tmpServicesFinal
-- sc query is used to query the entire service control manager and then filters
-- by anything with "SQL" in it's name. /I option ignores Case.
INSERT INTO #tmpServices EXEC xp_cmdshell 'sc query |find /I "sql"|find /I "service_name"'
-- Remove NULL records
DELETE FROM #tmpServices WHERE oOutput IS NULL
-- Cursor variables
DECLARE @curServNm VARCHAR(100)
DECLARE @cCMD VARCHAR(100)
DECLARE @cBinary VARCHAR(150)
DECLARE @cOwner VARCHAR(100)
DECLARE @cStartTp VARCHAR(100)
DECLARE cCursor CURSOR FOR
SELECT RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) )) AS ServiceName
FETCH NEXT FROM cCursor INTO @curServNm
WHILE @@FETCH_STATUS = 0
-- You can use different Options to query SC. For Example, use sc queryex to pull PID
SET @cCMD = 'sc qc "#SERVICENAME#"'
SET @cCMD = REPLACE(@cCMD, '#SERVICENAME#', @curServNm)
INSERT INTO #tmpServicesDetail EXEC xp_cmdshell @cCMD
DELETE FROM #tmpServicesDetail WHERE oOutput IS NULL
-- To extract any other piece of data, you should modify/add variable:
-- For Example: If I use sc queryex to get PID, then I would make the following changes:
-- Then You can Insert it into Temp Table
-- SELECT @cPID = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
-- FROM #tmpServicesDetail
-- WHERE PATINDEX('%PID%', oOutPut) > 0
SELECT @cBinary = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
WHERE PATINDEX('%BINARY_PATH_NAME%', oOutPut) > 0
SELECT @cOwner = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
WHERE PATINDEX('%SERVICE_START_NAME%:%', oOutPut) > 0
SELECT @cStartTp = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) ))
WHERE PATINDEX('%START_TYPE%:%', oOutPut) > 0
INSERT INTO #tmpServicesFinal (
FETCH NEXT FROM cCursor INTO @curServNm
-- Final result set
SELECT * FROM #tmpServicesFinal
-- Clean-up objects
IF OBJECT_ID('TempDB.dbo.#tmpServices') IS NOT NULL
DROP TABLE #tmpServices
IF OBJECT_ID('TempDB.dbo.#tmpServicesDetail') IS NOT NULL
DROP TABLE #tmpServicesDetail
IF OBJECT_ID('TempDB.dbo.#tmpServicesFinal') IS NOT NULL
DROP TABLE #tmpServicesFinal
Here are some sample results from my test environment:
Familiarize yourself with the SC windows command and run through some tests in your environment.
Walk through the code and modify it to meet your needs.
i like your approach for getting the information about the service account, because sc is a powerfull tool. But...
What will you do if you are not allowed - by policy of the customer - to use xp_cmdshell? I'm working for global companies and most of them have a "segregation of duty" which prevents me using xp_cmdshell.
The reason is quite simple - maybe the service account is not properly configured and is "local admin". Than I have the chance to manipulate the system with xp_cmdshell :(
I assume xp_readerrorlog is a better choice.
e.g. EXEC xp_readerrorlog 0, 2 will give you the latest sql agent error log and you can loock for the information about the servicea account.
Nevertheless - very informative and pretty fine written!
Tuesday, November 27, 2012 - 11:50:01 AM - John Garcia
Thank you for your comment. You are absolutely correct; xp_cmdshell needs to be enabled. In fact, I'll see if I can include another tip (if one doesn't already exists) to extract similar data using the xp_readerrorlog.
An interesting approach. Unfortunately xp_cmdshell is locked down in many environments so not really an option for many. I highly recommend Powershell and WMI for this type of stuff. I've never been a fan of trying to do operating system/configuration tasks from within SQL, it seems hokey to me. Keep it close to the operating system where it belongs IMHO.
At my past employer I read the local machines registry.
This is for the default instance:
DECLARE @ServiceaccountName varchar(250)
Depending on the sql version and/or instance names you'lle need to expand this.