March 30, 2009 at 7:52 am
Hi,
Is there a way to see what user login created a specific object, like a specific stored proc or a table?
And for that matter is there a way to see who ALTERED that object?
I am sure if I set up some kind of logging, I would be able to do this, but what about before hand?
Is this info - mainly the CREATE - not stored somewhere in the system tables?
Any help?
March 30, 2009 at 8:07 am
if not too much time has passed, the default trace can help:
-- obtain file name for Default Trace
declare @TraceFileName nvarchar(256)
set @TraceFileName = (select path from sys.traces where is_default = 1)
-- get general name for Default Trace (remove rollover number)
--set @TraceFileName =
-- substring (@Tracefilename, 1, (charindex ('\log_', @tracefilename) + 3)) + '.trc'
SELECT
trc_evnt.name
,dflt_trc.DatabaseName
,dflt_trc.ApplicationName
,dflt_trc.TextData
,dflt_trc.FileName
,dflt_trc.LoginName
,dflt_trc.StartTime
FROM fn_trace_gettable( @Tracefilename , NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
where name in('Object:Created','Object:Altered')
ORDER BY dflt_trc.StartTime DESC
Lowell
March 30, 2009 at 8:08 am
March 30, 2009 at 8:10 am
Casper (3/30/2009)
Is there a way to see what user login created a specific object, like a specific stored proc or a table?And for that matter is there a way to see who ALTERED that object?
Not unless you have some method of auditing or traceing in existence. It may be in the default trace, but that doesn't persist forever.
I am sure if I set up some kind of logging, I would be able to do this, but what about before hand?
Is this info - mainly the CREATE - not stored somewhere in the system tables?
No, it's not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2009 at 8:16 am
yeah i thought auditing would have to be the way out.
Just by the way - you said "if not too much time has passed" i can use the default trace.
How much time are we talking here? How much info is kept in here?
March 30, 2009 at 8:22 am
Casper (3/30/2009)
yeah i thought auditing would have to be the way out.Just by the way - you said "if not too much time has passed" i can use the default trace.
How much time are we talking here? How much info is kept in here?
the default trace tracks DDL changes, but DBCC stuff goes in there too.
It uses 5 files, each with a max size of 5 meg each...so at 25 meg of data max, it rolls over the oldest file, and starts tracing in a new one;so you have between 20 and 25 meg of data to sift thru.
Lowell
March 30, 2009 at 8:25 am
I found this info in SSMS, by right-clicking the DB name, choosing Reports>Schema Changes History. I don't know how long the info stays in there, as this is from the default trace, perhaps it is determined by how busy the DB is, but I have seen schema changes in there as old as 11 days.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 30, 2009 at 8:34 am
Stamey (3/30/2009)
I don't know how long the info stays in there, as this is from the default trace, perhaps it is determined by how busy the DB is, but I have seen schema changes in there as old as 11 days.
It has to do with how busy the server is, and how often the service is restarted. The default trace is max 5 files of 20 MB (I think) each and a new file is created whenever the service starts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply