Interview question

Question

Post reply

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Lowell SSC Guru Points: 323482 More actions · Answer 2

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!