In order to monitor all logons (from services on your local server or external connections) you’ll need a Server Logon Trigger.
I know there is nice SQL Server Audit functionality, but I needed something that’s easy to implement and would also work on Express-Editions and 2008 R2.
But regarding auditing in 2012, it is nice to know that:
All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions. For more information, see Features Supported by the Editions of SQL Server 2012.
Info about this on MSDN: Here.
Oh, it is wise to create a separate (new) database for Auditing.
For example, these server triggers fire once a user is already logged in and therefore it is a bit tricky to give all users Public access to Auditing tables/data.
In the code below, I forced the trigger to use the ‘SA’-account (and this will work even if the ‘SA’-account is DISABLED simply by the fact that the account has no need to login).
I recommend you guys to create a separate SQL Account for Auditing and rename the ‘SA’-account for security reasons.
Let me tell you that on my own server, I’ve had a lot of failed login attempts trying to login as ‘SA’ 🙂
Important: When you’re testing the trigger and it does occur that there is a syntax error in it.
Make sure you have a Query window in SSMS/SSDT which has an active connection.
You will not be able to login on your server 🙂
So just run the ‘DROP TRIGGER’-statement in that Query Window and prevent any annoying error messages (a lot about that on Google btw).
Execute parts of the T-SQL code as you see fit.
Download the code here:
Server Logon Trigger Code
The Server Trigger will be list in SSMS / SSDT in ‘Server Object’ -> ‘Triggers’