SQL SERVER USER ACTIVITY AUDIT SCRIPT HOW TO
SQL SERVER USER ACTIVITY AUDIT SCRIPT FULL
SQL SERVER USER ACTIVITY AUDIT SCRIPT CODE

if you end up locking yourself out due a logon trigger failing, come in through the DAC. Something you should definitely perfect in a dev environment. Check to see if any other sysadmin level accounts are connected. I believe logon triggers can fire on logouts, too. The logon trigger can see if a trace is running (or you could write to a semaphore table or something) and if so, it doesn't start a new one. after whatever period of idle time the connection is closed by the server.Дoes that still count as a logout event or would that trace keep running? Logout trigger catches it and ends the traceДЄdmin A's computer crashes. Insert trigger sees that write and starts a trace for AdminB Insert trigger sees that write and starts a trace for AdminA Then another logout event to wait for them to logout to end their trace. So if more than one person with sysadmin rights connects at once then multiple traces are started, but each one specific to that person. Then it writes a record to the SQLSaturday datagbase's changelog and at that point you have an insert trigger waiting for records to be written so AFTER The record is inserted it can grab the server login and then kick off a server side trace to start tracing their activity.? So then this creates a logon trigger that will wait until someone with sys admin privs touches the server. ('master', GETDATE(), ORIGINAL_LOGIN(), 'dbo', 'sysadmin role login!', 'Server', 'N/A') (DBName, EventTime, ServerLogin, DBUser,, ObjectName, TSQL) WHERE role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE NAME = 'sysadmin') ON srm.member_principal_id = sp.principal_id

They are similar and in my sleep-deprived state I'm making silly mistakes:Д¬REATE TRIGGER trigLogon_CheckForSysAdmin And it's actually a Logon Trigger (showed up in SP2) not a DDL trigger.

SQL SERVER USER ACTIVITY AUDIT SCRIPT CODE

Here is a code sample from the presentation I gave at SQL Saturday Jacksonville. Google to the rescue as always, I assume that is the answer to all my problems? рџ™‚ДЄctually, this is a good way to do it. Wasn't paying attention yet this morning it seems: What I'm saying is: How do I get it to keep the trace running after the server is rebooted, or after I log off the server? Is there a way to have it run as a service so I don't need to manually log in and start the trace or leave my account running to keep the trace running? Is there a way to set it as a service and execute traces with specific filters, settings, etc.

SQL SERVER USER ACTIVITY AUDIT SCRIPT HOW TO

someplace OFF the server so if the server is being 'abused' it isn't as easy to just wipe the trace logs in that table?Д¬ould you please advise on how to get SQL Profiler to run on the server constantly? I assume then the way to go would be to write it to a table on the server, and then setup an insert trigger (or something) to watch that table for activity then start archiving it off to emails, a network file location, etc. Just so I'm clear, I should use SQL Profiler to run on the server itself and then filter by the login name and have it only capture data / trace data for that set of login names? The account should NEVER be used except in the most extreme situations, so if someone does login and run 50 megs worth of text TSQL commands, I'd rather get 50,000 emails with the contents of those commands and be 'sure' rather than not know at all. I would like some way to capture the TSQL being sent to the server, write it to a table, log it, maybe send it in emails (since a log could be wiped but once the email is sent they can't get it back or destroy it).

SQL SERVER USER ACTIVITY AUDIT SCRIPT FULL

However, that won't really do much other than just say the login happened.Д«eing as how it is an account with full privs they could do whatever they wanted. I was thinking about just looking at the login events and when that account is used kicking an email to the DBA going 'Heads up, someone is logging in that isn't you.' stuff I guess.ДЄNYWAY, I need to keep an eye on that account and make DARN SURE it isn't being used 'willy nilly' to poke around.

This has access to the SQL server for random reasons, but the idea being that if the DBAs are all on a plane to Europe and it goes down, the other system admins can login with that account to do. There is also a domain account that ANY system administrator in the organization can use. Ok, we have our SQL servers, they have their usual group of DBAs.

Sql server user activity audit script