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.
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
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
Note: Microsoft Books online is a default reference of all articles .