Two ways to find out the sql server service accounts details in SQL server 2008 R2 either using SQL Server Configuration manager or using DMVs (Dynamic management Views)
1. Connect to SQL Server Configuration manager
2. Check out the service accounts, Here you will find all the sql server services and there logon credential.
3. Right click on the services to modify ->click on properties -> go to logon tab -> here you can see the existing sql server account information and can be modified by providing new login account.
By using T-SQL statement
SQL Server 2008 r2 has introduced new dmvs in service pack 1, so you need to install SP1 before execute t-sql command against these dmvs. In SQL Server 2008 R2 SP1 and later, we can find the SQL Server service account using T-SQL without using extended stored procedures (xp_servicecontrol).
EXEC xp_servicecontrol N’querystate’,N’MSSQLServer’
EXEC xp_servicecontrol N’querystate’,N’SQLServerAGENT’
After installing Service Pack 1 for SQL Server 2008 R2, you can query using “sys.dm_server_services” for the service accounts used by SQL Server. You can also use the sys.dm_server_registry DMV to find the information, although this is not as clean.
Execute the below query to find out status of the sql server services.
Select servicename FROM sys.dm_server_services
Where status = 1 –> to find out Stopped services
Below are the values which indicates the current status of the services
2 Other (start pending)
3 Other (stop pending)
5 Other (continue pending)
6 Other (pause pending)
SELECT key_name, value_name, value_data FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
After executing above query, it will display the SQL SERVER AGENT registry key value.Here we can filter the output based on key_name, register_key