Interview question

• patla4u

  SSCrazy

  Points: 2103

  More actions

  October 3, 2011 at 12:10 pm

  #243969

  Hello All,

  I want to know that...if user can insert,update and delete some records and i want to track all information(means user name ,time,which database) then how can i do in SQL server?

  Can any one explain me?

  Thanks

  L

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  October 3, 2011 at 12:19 pm

  #1389732

  Lots of ways to do this. It's essentially auditing the user's actions.

  Search for auditing, read about different solutions. You have trace, xEvents, triggers, SQL Server Audit, and more.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 3, 2011 at 12:19 pm

  #1389733

  patla4u (10/3/2011)

  ---

  Hello All,

  I want to know that...if user can insert,update and delete some records and i want to track all information(means user name ,time,which database) then how can i do in SQL server?

  Can any one explain me?

  Thanks

  L

  there are lots of ways, so they were expecting you to be familiar with them. it also depends on what you need to capture; who did what tracking is differnet from teh ability to see what data changed from valueA to valueB.

  you could Create a specific DMLtrace or enable C2 Auditing, which is just another trace, but you need to be aware of the impact.

  you can use SQL Change Data Capture.

  you can use SQL Auditing

  you could create your own suite of triggers on each table to audit the insert update and delete operations.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!