Need SQL Query

Question

Post reply

Need SQL Query

bala2013

SSChasing Mays

Points: 607
More actions
August 1, 2013 at 6:37 am

#297149

Dear All,
need sql query for following scenario:
Development server somebody is altering the table column or delete records,we need sql query for find the systemname and username.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You need to create DDL triggers for that. Start from here:

http://msdn.microsoft.com/en-us/library/ms190989(v=sql.105).aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

rodbloke SSCommitted Points: 1612 More actions · Answer 2

Hi, the script below will look through the current default trace log for tables Altered/Created/Deleted. Just run it under the context of the DB in question AND enter your specific table name in the WHERE clause to filter it.

It won't show data deletions though. If you need to track past deletions then you may need to get hold of a Log reader to see whats gone on (providing you have the logs intact or backed up).

For detecting FUTURE deletes could include: (dependent on your situation)

Using an SQLtrace (DEV so are potential performance hits acceptible?).

Setting up an Audit spec (What Edition are you running?)

Set up a Triggers to log elsewhere? (can you amend the schema)

...and there are probably other methods that I can't think of off the top of my head at the minute.

-------------------------------

DECLARE @filename nvarchar(1000);

-- Get the name of the current default trace

SELECT @filename = cast(value as nvarchar(1000)) FROM ::fn_trace_getinfo(default) WHERE traceid = 1 and property = 2;

-- view current trace file

SELECT LoginName, Starttime, ApplicationName, Hostname,object_name(objectid) Obj_name, name Eventname

FROM ::fn_trace_gettable(@filename, default) AS ftg

INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

WHERE TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' ) AND

DatabaseName <> 'tempdb'

and EventSubClass = 0

--Filter to user defined tables

and objecttype=8277

--Filter on table Name

AND object_name(objectid)='{ ******** INSERT TABLE NAME HERE **********}'

ORDER BY ftg.StartTime;

Durai Samuel Mr or Mrs. 500 Points: 551 More actions · Answer 3

Please check if this link helps

http://www.dotnetbites.com/find-user-modified-table-stored-procedure

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Durai Samuel (8/1/2013)

Please check if this link helps

http://www.dotnetbites.com/find-user-modified-table-stored-procedure%5B/quote%5D

That's got some serious errors in it. Although it happens frequently, no result set is guaranteed to be in the same order as the PK unless you have and ORDERY BY on the PK column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Jeff Moden (8/1/2013)

Durai Samuel (8/1/2013)
Please check if this link helps
http://www.dotnetbites.com/find-user-modified-table-stored-procedure%5B/quote%5D
That's got some serious errors in it. Although it happens frequently, no result set is guaranteed to be in the same order as the PK unless you have and ORDERY BY on the PK column.

Jeff I think you meant to respond to a different thread. This one perhaps? http://www.sqlservercentral.com/Forums/FindPost1480089.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/