Two ways to find out the sql service account in SQL server 2008 R2
By using SQL server Configuration manager
1. Connect to SQL server Configuration manager
2. Check out the services node
Here you will find all the sql services, ort no and there credential.
3. To modify the same right click on the services à click on properties à go to logon tab à here you can see the existing account information and can be modified by providing new login account.
By using Transact SQL statement in SQL Server
We need to install SP1 for the Transact SQL statement. In SQL Server 2008 R2 SP1 and later, we can find the SQL Server service account using T-SQL, without using extended stored procedures.
After SP1 for SQL Server 2008 R2, there is a new DMV that allows you to query for the service accounts used for the various SQL Server services. You can also use the sys.dm_server_registry DMV to find the information, although this is not as clean.
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
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