track the user activities on a database ?

Article
04/06/2016

Question

_{Wednesday, April 6, 2016 3:13 AM}

Hi All,

Client requested to track all activities performed on server by the particular user with SQL text. How can i set the audit for particular user.

Please guide me to work on this request.

Thanks in Advance,

rup

All replies (8)

_{Wednesday, April 6, 2016 9:07 AM ✅Answered}

You can run a server side trace file without affecting performance

http://www.toadworld.com/platforms/sql-server/w/wiki/10400.the-server-side-trace-what-why-and-how

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Wednesday, April 6, 2016 9:14 AM ✅Answered}

You can use a server-side trace or even an Extended Event session (even lower overhead).

Tibor Karaszi, SQL Server MVP (Web Blog)

_{Wednesday, April 6, 2016 5:56 PM ✅Answered}

You can run a server side trace file without affecting performance

...

"Without affecting performance"?

That is an yet unheard myth

Here you can see a complete comparison of SQL Trace variations vs Extended Event session variations oveahead with a high volume event: Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load

SQL Trace is deprecated since SQL Server 2012 for good reasons (performance being one of them)

You can try using Auditing and if that is not sufficient revert to Extended Events which are the underlying technology for the Auditing Feature as well. It really depends on the exact list of events that you are after as well as the data you need to be able to see for the events - and the Version of SQL Server.

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform
www.SarpedonQualityLab.com | www.andreas-wolter.com

_{Wednesday, April 6, 2016 5:15 AM | 1 vote}

I think your best bet is going to be a server side trace. You can use Profiler to build the trace. It's the only way to capture what all is being done. There are ways of obfuscating operations with the Audit object. A server side trace will typically catch most of these

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

_{Wednesday, April 6, 2016 6:14 AM}

Thank You for the reply Uri Dimant.

But we can't run the profile entire day right. it's using more resources. can you please suggest any other way to get it done.

Thanks,

rup

_{Wednesday, April 6, 2016 7:39 AM}

May be something called as SQL Server Audit can help you, as it is very lite when compared to SQL profiler(due to target auditing).

The logs are easily viewable in the event viewer.

_{Thursday, April 7, 2016 5:45 AM}

You can run server side trace to track all activities performed on server by the particular user. for this you need to create a trace using SQL Profiler. After this, you will get the T-SQL to create and execute the trace on SQL Server.

_{Tuesday, April 12, 2016 9:39 AM}

Thanks Jason for your reply. can you please let me know what are the events i have to add to get the users (2 or more) activities with sql text.

Thanks in Advance,

rup

Share via

track the user activities on a database ?

Question

All replies (8)

Additional resources