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

By:   |   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.

SQLServer2000 AuditLevel

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.

SQLServer2005 LoginAuditing

*** 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
SQLServer2000 Profiler Login
2 General Tab - Enter the following information:
  • Trace Name
  • Trace Template
  • Save the Trace to a file or table
SQLServer2000 Profiler GeneralTab
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.

SQLServer2000 Profiler EventsTab
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.

SQLServer2000 Profiler DataColumnsTab
5 Filters Tab - For the LoginName option, expand it to the 'LIKE' option and enter 'sa' as a parameter SQLServer2000 Profiler FiltersTab
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.

SQLServer2000 Results

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

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=



























get free sql tips
agree to terms