Who CREATED and/or ALTERED an object

Question

Post reply

Who CREATED and/or ALTERED an object

Casper101

Hall of Fame

Points: 3903
More actions
March 30, 2009 at 7:52 am

#132747

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?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--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!

John.Sansom SSCertifiable Points: 7335 More actions · Answer 2

Hi,

You can use DDL Triggers to record/log when a CREATE TABLE statement is issued. This can in turn be used to record details of the login that executed the statement.

See the following article for an example:

http://www.sqlservercentral.com/articles/Auditing/64176/[/url]

Hope this helps.

Cheers,

John Sansom (@sqlBrit) | www.johnsansom.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Casper101 Hall of Fame Points: 3903 More actions · Answer 4

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?

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--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!

Stamey SSCertifiable Points: 5600 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass