SQL Server service account in SQL Server R2

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

open sql server configuration manager

2.   Check out the service accounts, Here you will find all the sql server services and there logon credential.

sql server service accounts

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.

change the sql server service account

sql server service (MSSQLSERVER) properties

 

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

1 Stopped

2 Other (start pending)

3 Other (stop pending)

4 Running

5 Other (continue pending)

6 Other (pause pending)

7 Paused

 

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

 

Leave a Reply

  

  

  


− 5 = 3