I have a requirement where I need to audit whenever the 'sa' login account logs into an SQL Server instance (mostly SQL 2008R2, but also some SQL2005 and SQL2012 shop).
Additionally, I need to know to which database (and podssibly the statement) is used by the captured connection.
I did some research and vetted some options and I chose to go with server logon trigger
. I founds some advice online and decided to use the attached script to 1. create a table to store the captured logins and 2. To create the actual trigger.
The attached script works perfectly well. The only thing that I cannot firgure out is how to modify this script to also capture and store in the table also the database name (I must have this info) where the login is connecting to and\or the statement (could go without that) of the code being issued.
Any help would be greatly appreciated.