Who is logging in as the sa login in SQL Server?

By:   |   Updated: 2007-04-03   |   Comments (1)   |   Related: > Security


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?


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:
  • Launch Profiler - Start | Run | All Programs | Microsoft SQL Server | Profiler
  • New Profiler session - File | New Trace
  • Login - Select the SQL Server instance and login credentials to login
  • Trace Properties - This screen will enable proper configurations
2 General Tab - Enter the following information:
  • Trace Name
  • Trace Template
  • Save the Trace to a file or table
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:
  • Cursors
  • Database
  • Errors and warnings
  • Objects
  • Security Audit
  • Sessions
  • Stored procedures
  • TSQL

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:
  • EventClass
  • TextData
  • ApplicationName
  • NTUserName
  • LoginName
  • DBUserName
  • HostName
  • NTDomainName
  • LoginSid

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

Last Updated: 2007-04-03

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources

Comments For This Article

Tuesday, July 10, 2012 - 2:58:36 AM - VAhid Back To Top (18413)


I have a database server that users are connected through to it but i dont know a user is that drop database on server

i write a trigger and log history but again user can delete my database on server and trigger cant prevent that it



Trigger [LogDB] onall









'You must contact a DBA before dropping or altering tables!'














@CommandText =EVENTDATA().value














sys.sysprocesseswhere spid=




























Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

SQL Server Permissions List for Read and Write Access for all Databases

List SQL Server Login and User Permissions with fn_my_permissions

get free sql tips
agree to terms