Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Checking SQL Server Services Owner

MSSQLTips author John Garcia By:   |   Read Comments (6)   |   Related Tips: More > Database Administration
Problem

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.

Solution

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.

USAGE:
    sc <server> [command] [service name] <option1> <option2>...

The option <server> has the form "\\ServerName"

Further help on commands can be obtained by typing: "sc [command]"

Commands:

  • query - Queries the status for a service, or enumerates the status for types of services.
  • queryex - Queries the extended status for a service, or enumerates the status for types of services.
  • start - Starts a service.
  • pause - Sends a PAUSE control request to a service.
  • interrogate - Sends an INTERROGATE control request to a service.
  • continue - Sends a CONTINUE control request to a service.
  • stop - Sends a STOP request to a service.
  • config - Changes the configuration of a service (persistent).
  • description - Changes the description of a service.
  • failure - Changes the actions taken by a service upon failure.
  • failureflag - Changes the failure actions flag of a service.
  • sidtype - Changes the service SID type of a service.
  • privs - Changes the required privileges of a service.
  • qc - Queries the configuration information for a service.
  • qdescription - Queries the description for a service.
  • qfailure - Queries the actions taken by a service upon failure.
  • qfailureflag - Queries the failure actions flag of a service.
  • qsidtype - Queries the service SID type of a service.
  • qprivs - Queries the required privileges of a service.
  • qtriggerinfo - Queries the trigger parameters of a service.
  • qpreferrednode - Queries the preferred NUMA node of a service.
  • delete - Deletes a service (from the registry).
  • create - Creates a service. (adds it to the registry).
  • control - Sends a control to a service.
  • sdshow - Displays a service's security descriptor.
  • sdset - Sets a service's security descriptor.
  • showsid - Displays the service SID string corresponding to an arbitrary name.
  • triggerinfo - Configures the trigger parameters of a service.
  • preferrednode - Sets the preferred NUMA node of a service.
  • GetDisplayName - Gets the DisplayName for a service.
  • GetKeyName - Gets the ServiceKeyName for a service.
  • EnumDepend - Enumerates Service Dependencies.

The following commands don't require a service name:

  • sc <server> <command> <option>
  • boot - (ok | bad) Indicates whether the last boot should be saved as the last-known-good boot configuration
  • Lock - Locks the Service Database
  • QueryLock - Queries the LockStatus for the SCManager Database

Source - http://www.windowsreference.com/windows-server-2008/sc-utility-control-windows-services-from-command-line/.

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 
(oOutput VARCHAR(1024))

CREATE TABLE #tmpServicesDetail
(oOutput VARCHAR(1024))

CREATE TABLE #tmpServicesFinal
(ServiceName VARCHAR(100),
ServiceOwner VARCHAR(100),
ServiceStartTp VARCHAR(100),
ServiceBinary VARCHAR(150))

-- 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
FROM #tmpServices

OPEN cCursor
FETCH NEXT FROM cCursor INTO @curServNm

 WHILE @@FETCH_STATUS = 0

  BEGIN

   --  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)) )) 
    FROM   #tmpServicesDetail
    WHERE  PATINDEX('%BINARY_PATH_NAME%', oOutPut) > 0
    
    SELECT @cOwner = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) )) 
    FROM   #tmpServicesDetail
    WHERE  PATINDEX('%SERVICE_START_NAME%:%', oOutPut) > 0
    
    SELECT @cStartTp = RTRIM(LTRIM(SUBSTRING(oOutPut,PATINDEX('%:%', oOutPut)+1, LEN(oOutPut)) )) 
    FROM   #tmpServicesDetail
    WHERE  PATINDEX('%START_TYPE%:%', oOutPut) > 0

    INSERT INTO #tmpServicesFinal (
     ServiceName,
     ServiceOwner,
     ServiceStartTp,
     ServiceBinary)
    VALUES(
     @curServNm,
     @cOwner,
     @cStartTp,
     @cBinary)

FETCH NEXT FROM cCursor INTO @curServNm
END

CLOSE cCursor
DEALLOCATE cCursor

-- 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:

Next Steps


Last Update: 11/23/2012


About the author
MSSQLTips author John Garcia
John Garcia is a SQL Server Database Administrator in Alpharetta, GA with over 12 years of experience.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, February 28, 2014 - 9:11:21 AM - Rudy Read The Tip

If you cannot use xp_command shell, which is not recommended, you can use my code here: http://sqlsurgeon.blogspot.ca/2011/05/sql-server-service-check-utility.html

It will give you information without using xp_command shell.

 

 


Thursday, February 21, 2013 - 11:14:13 AM - Mark Read The Tip
At my past employer I read the local machines registry. This is for the default instance: DECLARE @ServiceaccountName varchar(250) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @ServiceAccountName OUTPUT, N'no_output' SELECT @ServiceaccountName Depending on the sql version and/or instance names you'lle need to expand this.

Thursday, December 20, 2012 - 4:43:42 PM - bkr Read The Tip

Given information was really nice.


Tuesday, December 18, 2012 - 7:05:23 AM - Chris S Read The Tip

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.


Tuesday, November 27, 2012 - 11:50:01 AM - John Garcia Read The Tip

Uwe,

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.

Thanks,

JG


Saturday, November 24, 2012 - 1:03:48 AM - Uwe Ricken Read The Tip

Hi John

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!




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.