Keep track of who accessed\modified the database except application user\login

Question

Keep track of who accessed\modified the database except application user\login

adisql

SSCrazy

Points: 2893
More actions
May 6, 2020 at 5:45 pm

#3749453

Hi,
SQL version: sqlserver2016 standard .
I am looking to keep track of the details for audit purpose who access to database, from which machine , and data modifications.
event_time
Client Machine Name\IP
UserName\Loginname
server_instance_name
database_name
schema_name
object_name
SQL statement
Any help here ?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 1

I may be mistaken but I am pretty sure what you are asking for does not exist.

If it did exist, I can't imagine the performance hit that would cause in a live system and you'd have the risk of deadlocks happening with every query that got executed against the databases.

On top of that, who is going to look at that data?

I think a better approach is to pick the tables you care about data changes and put some auditing on those with CDC or triggers.

Inside of your stored procedures you can keep logs of what parameters were passed in and log things that way, but otherwise I'm thinking something like CDC is going to be your best bet, but it won't be in the exact format you are requesting.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

You may want to consider a third party product to do this, especially if it's for compliance with auditing. One of the advantages of using the third party products is that any changes to the audit can also be captured with those products which auditors seem to like.

Sue

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

First, I would look at Server and Database Audits.

Second, why are you ignoring data changes from the application(s) user/login? Those should be audited as well as changes made outside the application.