Monitor user login connected to SQL Server

Monitor user login connected to SQL Server

 

SA and Windows Administrator login should be disabled in SQL Server due to security reason. I am not going to discuss those reasons in this article as there are many pro/cons and rules/regulations of some industry associated with it. Once we start debate on this, it is never ending so there is no meaning to raise this issue in this article.

 

I, personally, believe that SA and Administrator account either should be disabled or should not be used until real emergency comes when we MUST have to use it.

 

I have my monitoring application developed in Asp.Net which keeps an eye on every aspects of SQL Server I need. One of them is to check whether any developer or application is using SA or Administrator account. I use following SQL Server view which keeps an eye on this.

[sourcecode language=”sql”]

CREATE VIEW View_GetLoginDetails

AS

SELECT TOP 5

Host_Name as HostName,

login_name AS LoginName,

count(*) as Connections,

CASE

WHEN login_name = ‘sa’ OR login_name LIKE ‘%administrator%’ THEN ‘red’

ELSE NULL

END AS BackColor

FROM

sys.dm_exec_sessions

GROUP BY

Host_Name, login_name

ORDER BY

BackColor DESC, Connections DESC

[/sourcecode]

Few other links for DBA to check for different important things:

  • Capture Schema Change in SQL Server to keep up history (Click Here)
  • Capture long running query in SQL Server (Click Here)
  • Analyze IO disk pressure in SQL Server (Click Here)
  • Transactions/Sec and Batch Requests/Sec performance counter in SQL Server (Click Here)
  • Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions (Click here)
  • Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find locked table name with duration and count in SQL Server (Click Here)
  • Find Blocking in SQL Server and use it to send an alert (Click Here)

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Monitor user login connected to SQL Server”

Comments are closed.