Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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

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:
  • 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


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Hello

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

 

CREATE

Trigger [LogDB] onall

server

For

DROP_database,ALTER_database,ALTER_TABLE,DROP_TABLE,CREATE_DATABASE,Drop_Trigger

as

set

nocount

on

PRINT

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

rollback

Declare

@CommandText

nvarchar(2000),

@ComputerName

nvarchar(100),

@ApplicationName

nvarchar(100),

@LogiName

nvarchar(100),

@LogDate

DateTime

SELECT

@CommandText =EVENTDATA().value

(

'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'

)

select

@ComputerName

=HostName,

@ApplicationName

=Program_name,

@LogiName

=suser_sname(),

@LogDate

=GetDate()

from

sys.sysprocesseswhere spid=

@@Spid

Insert

LogError.dbo.LogEvents

(

CommandText

,

ComputerName

,

ApplicationName

,

LogiName

,

LogDate

)

values

(

@CommandText

,

@ComputerName

,

@ApplicationName

,

@LogiName

,

@LogDate

)


Learn more about SQL Server tools