SQL Server – How to Find Who Used SA for Login

sql server

In an organization if a community used one SA account for a database. So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?

Best Answer

community used one SA account for a database.

This is a big security risk, since your "SA" password is a known password in your organization. "SA" is the most elevated privilege account that if misused will cause a disaster and/or data breach by internal people.

I agree with @AaronBertrand, that you should not give "SA" password out to people you do not trust. IMHO, "SA" should be disabled (or atleast change its password on frequent basis), since there are other ways wherein you can give people access following principle of least privilege to get their work done. SQL Server does not have LAPS (like windows server now has), so you have to design and implement a process that changes "SA" password on frequent basis. Below are some things that you can do to limit sa use (see reference cited below for more information article by - K. Brian Kelley):

  • Set a hard to guess password.
  • Rename sa.
  • Disable sa.
  • Ensure that no other accounts exist named sa.

So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?

Given above points, SQL Server does not have an easy way of detecting what users/applications are using "SA" to connect. I have implemented a light weight server side trace that can be used to find out applications/users/host name that uses "SA" to connect. Script can be found here and below is the sample output :

enter image description here

For complete automation, you can use TRACE_FILE_CLOSE WMI event to trigger import of trace file into sql server database table.

Alternatively, you can gradually move away from SQL Server authentication to Windows Authentication.

Refer to :

Related Question