0

I need to implement an auditing for everyone logging in to SQL Server 2014 using the sa account. User should be able to provide user id and reason why he/she is logging as sa, which should be stored in a specific table with a timestamp.

What is the best way to do this? What language/techniques I should use?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hanna Vasilenka
  • 11
  • 1
    maybe this can help https://learn.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers – GuidoG Jan 09 '18 at 13:08
  • 3
    Whatever you store in this table... it can be deleted by sa. You shouldn't be letting people log in as sa at all – Nick.Mc Jan 09 '18 at 13:11
  • 1
    The proper way to do this is to use windows authentication. _Don't hand out the sa password!_ – Nick.Mc Jan 09 '18 at 13:32
  • Thanks for the advises! My manager suggested to use Perl or Python. I'm new to coding so no idea where to start.. – Hanna Vasilenka Jan 09 '18 at 14:22
  • Your manager doesn't have a clue. How on earth could you possibly use Perl to enforce auditing in a database? You can't. No matter what you use to write to this audit table, someone with the `sa` password can just log in and delete or edit audit entries. I don't know what you're trying to solve, but if you've handed out the sa password, you need to change the password, then take a step back and look at your security. – Nick.Mc Jan 10 '18 at 01:04
  • The 'proper' way to implement login auditing is to use serve audits https://solutioncenter.apexsql.com/how-to-setup-and-use-sql-server-audit-feature/ but again, any sa user can just turn them off and alter them. – Nick.Mc Jan 10 '18 at 02:50
  • I implemented it =) If you would like you can watch the video by link https://youtu.be/7GWz_YW1teI – Konstantin Jan 21 '18 at 16:31

0 Answers0