Checking Your Service Account with T-SQL
Somehow this slipped by me, but there were some new DMVs added in SQL Server 2008 R2 SP1. I suspect my test machines were mostly SQL Server 2008 or SQL Server 2012, and I hadn’t been paying attention to the changes in SP1.
You can now use T-SQL to check for services information, as well as registry information, without using extended stored procedures or any hacks of xp_cmdshell. There are two new DMVs:
These were not present in the RTM of SQL Server 2008 R2, but after installing SP1, they appear. The KB article for SQL Server 2008 R2 SP1 includes a note that new trace templates for Profiler are included, but I did not see a note about these two DMVs.
So much for not adding features in Service Packs.
In any case, you can query the sys.dm_server_services for service account information. You will get the service name, the startup type, the account, and more.
If you aren’t a Windows administrator on your SQL Server boxes, you should still be able to get information regarding the services from this DMV as long as you have VIEW SERVER STATE permission.