Problem
Not knowing which applications or people on your team that are using the sa login should be a major cause for concern in security conscious organizations. If the password for this standard SQL Server login is known by more than 1 team member, tracking changes committed by the sa login can be challenging. Since, the sa login has rights to execute all commands (highest level of rights in SQL Server) knowing who and what applications that are the sa login should cause concern for organizations that are less security conscious. Regardless of my situation, how can I find out who is using the sa login?
Solution
Since the sa login has the highest level of rights in SQL Server, ships with the product by default and may be used in a number of known and unknown capacities, understanding who and what applications are using this login should be validated on a regular basis. Taking the approach of changing the password and see what breaks should be unacceptable for most organizations.
Auditing Logins in the SQL Server Error Log
The native audit level configuration is a simple configuration that can help to determine if the sa login is used in the SQL Server instance. What this configuration does is records all of the successful (or failed or both) logins into a particular SQL Server instance. You will not know the host name, application, etc. However, this configuration will enable you to determine if the sa login is being used in the first place. Once you have this information, then you can dig deeper with SQL Server Profiler outlined below.
| SQL Server 2000 and 2005 Login Auditing | ||
| ID | SQL Server Platform | Screen Shot |
| 1 | SQL Server 2000 – To enable the native audit level configuration, navigate to Enterprise Manager | root | right click on the SQL Server name | select ‘Properties’ | ‘Security’ tab and select the Audit Level radio button. |
|
| 2 | SQL Server 2005 – To enable the native audit level configuration navigate to Management Studio | login to the database engine instance | right click on the SQL Server name | select ‘Properties’ | select the ‘Security’ page and select the Login Auditing radio button. |
|
*** NOTE *** – Depending on the frequency of the logins in your SQL Server instance, the SQL Server error logs can become large very quickly. When you go to access them via Enterprise Manager or Management Studio you may have a delay or these applications may seem to lock up. As such, either increase the number of active SQL Server error logs and recycle the error logs more frequently or access the SQL Server error logs from DOS.
SQL Profiler Auditing
Once you know if the sa login is being used, then you can begin to dig deeper with SQL Server Profiler which can identify the host name, application, etc. for the team member or application that is using the sa login. Below outlines the steps to do so:
SQL Server 2000 | ||
| ID | Description | Screen Shot |
| 1 | Start a new SQL Server Profiler Trace by the following steps:
|  |
| 2 | General Tab – Enter the following information:
|  |
| 3 | Events Tab – Depending on the potential usage of the sa password in your environment you may need to include some or all of the following event classes:
To include a event class, select the event class in the ‘Available event classes list’ and then press the ‘Add >>’ button for inclusion in the ‘Selected event classes’ listing. |  |
| 4 | Data Columns Tab – Depending on the potential usage of the sa password in your environment you may need to include some or all of the following data columns:
To include ‘Selected data’, select one of more of the ‘Unselected data’ items and then press the ‘Add >>’ button for inclusion in the ‘Selected data’ listing. |  |
| 5 | Filters Tab – For the LoginName option, expand it to the ‘LIKE’ option and enter ‘sa’ as a parameter |  |
| 6 | Start Profiler – To start Profiler click the ‘Run’ button and review the results in the interface, file or table selected during the General tab configuration. From this example, I can see the HostName ‘JTKLAPTOP’ is using the sa login via SQL Query Analyzer. |  |
SQL Server Profiler Alternatives
Another option to address this need is to leverage a third party SQL Server auditing product. Some of these products have intuitive interfaces that can be configured in an advanced manner which can meet your specific auditing needs for a login, application or team member. For additional information check out the Auditing Products for SQL Server tip on MSSQLTips.com.
Next Steps
- Knowing who and what applications are using the sa login is critical in trying to understand the implications when this password needs to change. In addition, should be a checks and balances between the needed rights for the team member or application and the granted rights.
- If the highest level of rights in SQL Server are not needed by a team member of application, then drill into the rights that are needed and grant those rights.
- Check out the earlier tips in the sa series on MSSQLTips.com:
- For additional information about SQL Server Profiler, reference these tips:
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
