Question :
I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server.
I have two SQL Servers, say A and B. I want to log all logons using a logon trigger from server A in server B, and it is important that my database be able to insert the log into A, but it should not have delete or update access on B.
I want the log to be made, but I do not trust my sa or other people who have full access on A.
In brief, Server A can insert Logs but can not do anything more than that, and when I want to monitor the logons on A the only possible solution should be doing it on B.
How can I achieve this situation?
Answer :
I want to log all logons using a logon trigger from server A in server B.
Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit.
You can even set up a light weight server side trace and then define a WMI event notification – TRACE_FILE_CLOSE to automatically load the trace data into a table when it rollsover.
Another option is – you can use the script for Logon Trigger posted by me. Note that it wont work for the existing connections and it a limitation of Logon triggers. All new connections will go through the Logon Trigger.
it is important that my database be able to insert the log into A, but it should not have delete or update access on B.
You can use transactional replication by just replicating the LOGON Table to server B (in your case). Make sure you choose – Do not replicate DDL changes option.
when I want to monitor the logons on A the only possible solution should be doing it on B.
You can create a view using Linked server and just give select permission to the view. This way you can control who can access the info.
As a side note, make sure you do not return a full data set in the view, rather just limit it with last 10 -15 days of login audit, so when a user queries the data, it will return a small amount of data only.
SQL Server Audit is your best option if you are using Enterprise Edition, anything less (Standard) does not allow database level auditing.
However you could just as well setup Extended Event session to do the auditing for you at the database level. You can set the target to a file or ring buffer. Ring buffer would require period polling to persist the data to a table so for archive a file target would likely work better. Then you can set that up to roll over and then archive the file to a table for easier reporting.