How to find the SQL Server Service Account

What is the service account running your instance of SQL Server?

A lot of DBAs probably know right off the top of their head–maybe because they’re using the same account for every instance. Have you ever had to audit every service account on every instance? If you’ve never done an audit, there’s a good chance a surprise might be lurking for you.

What’s the easiest way to check?

If you have xp_cmdshell enabled, you can do a really quick check using the command line command whoami. This simply returns the windows account that executed the whoami command. Since SQL Server is talking to your command prompt, viola–you get your service account back.

Life isn’t always that easy. xp_cmdshell is often disabled for security purposes, so what do you do then?

My answer is to check the registry. If you use regedit look at HKLM\SYSTEM]CurrentControlSet\Services\<instance>, you’ll see a value named “ObjectName” that lists the service account. Time to script it.

(Ok, sure, you could use Get-Service in PowerShell, but PoSh is not usually my first choice. I’d rather do it in T-SQL.)

I use the unsupported/undocumented system extended procedure xp_regread. In about a dozen lines of code, you can quickly find out your configured service account. This code looks at the configuration of the Windows service, and determines the name of the service account–pretty straightforward.

DECLARE @SqlServiceAccount varchar(50),
        @RegistryKeyPath varchar(50)

--Determine path based on Default or named instance
SELECT @RegistryKeyPath = 'SYSTEMCurrentControlSetServices'
         + CASE
             WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'MSSQLSERVER'
             ELSE 'MSSQL$' + @@SERVICENAME
           END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
        @key = @RegistryKeyPath,
        @value_name = 'ObjectName',
        @value = @SqlServiceAccount OUTPUT

SELECT @SqlServiceAccount AS SqlServerServiceAccount

What if you have hundreds of instances you manage?

Hopefully you already have some method or process to run this against all your instances and get the answer. I have some system monitoring that collects configuration information from all our instances, and this is one of those pieces of info.

For ad-hoc audits, I like SQL’s (built-in and free) Central Management Server feature to run scripts against multiple servers. If you’re not familiar, check out Mike Hillwig’s (blog|twitter) post Do More With Less: SQL CMS and MSX.

UPDATE:
Mike Hillwig pointed me to another blog post he wrote about Temporarily Enabling xp_cmdshell. That might be a good option for you, too (especially since the xp_cmdshell trick is quick and easy to remember).  I still prefer the xp_regread method for my monitoring code, and depending on your security policy, you might not be able to use xp_cmdshell, even temporarily. But the more weapons in your arsenal, the more dragons you can slay–so pick the method that’s best for you.