Audit 'SA' login connections to SQL Server
Hello Experts,
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.
Thank you,
RICUser
C--Users-pchter01-Desktop-Create-Table--
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.
Thank you,
RICUser
C--Users-pchter01-Desktop-Create-Table--
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You did nothing wrong, obviously DatabaseName and TSQLCommand are not data of any event.
I can only quote once more:
And voila: http://msdn.microsoft.com/en-us/library/hh213611.aspx
So unfortunately database name and statement is not part of Logon events.
There's nothing we can do about it, so you will need to log more events or use some other way. Eg use login auditing in the security options of the server properties.
Bye, Olaf.
I can only quote once more:
To obtain the schema returned for an event, use Index or Search to locate the topic for the event in SQL Server Books Online
And voila: http://msdn.microsoft.com/en-us/library/hh213611.aspx
So unfortunately database name and statement is not part of Logon events.
There's nothing we can do about it, so you will need to log more events or use some other way. Eg use login auditing in the security options of the server properties.
Bye, Olaf.
ASKER
Note: Even though the above has been selected as a solution it does not accomplish 100% of the objective. An additional process will need to be implemented to be able to capture\audit database scope and statement issued.
Thank you Olaf!
RICUser
Thank you Olaf!
RICUser
You're welcome.
Just be aware, further event triggers mostly cover DDL, but of course also DML inserts and updates. You won't get an event for data reading, though.
If using login auditing, you can audit all logins, successful or not, but not limited to sa. You can read from the log via xp_readerrorlog.
Bye, Olaf.
Just be aware, further event triggers mostly cover DDL, but of course also DML inserts and updates. You won't get an event for data reading, though.
If using login auditing, you can audit all logins, successful or not, but not limited to sa. You can read from the log via xp_readerrorlog.
Bye, Olaf.
ASKER
Obviously I am doing something wrong here. I added the two extra columns to the table definition then added them to the trigger definition using the syntax you provided above. The trigger is working still, and loading the data into the table, however the DB name and statement are all NULLs - see new attachment.
Can you check my new script ans see what am I doing wrong ?
Thank you,
RICUser
C--Users-pchter01-Desktop-Results-of-Scr
C--Users-pchter01-Desktop-Create-Table--