Who Created A Table

Question

Post reply

Who Created A Table

J39L4753

SSCrazy

Points: 2803
More actions
March 25, 2010 at 8:52 am

#139952

Afternoon,
Is there anyway of finding out which database user has created a table?
What's happened is that we have a table holding all job details, this has several constraints on fields and a trigger that is used to send data to another table for some external work we do.
The otherday my work I do for external people stopped producing data, first thought was the time of year it was not much work would be going through. but this didnt feel right. further investigation shows the original table has been renamed and a new jobs table created, but with the missing parts.
How can I find which database user has done this?

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

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Question that I have....is why would you ever allow a user to modify in any way, the DDL?

You need to remove all permissions for that.

Unless you have some type of auditing, such as DDL auditing with SQL 2008, then no, there is no way of knowing that I can think of.

Andrew SQLDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

DDL triggers are also available in SQL Server 2005, as is event notification. Both could be used to set up DDL auditing.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Sorry

I meant to type 2005. My mind is not working well today.

Andrew SQLDBA

Lowell SSC Guru Points: 323518 More actions · Answer 4

the default trace would have that information if not too much time has past; the default trace captures DDL, but not DML changes.

easiest way is to point SSMS to the database in question,right click on Reports>>Standard Reports>>Schema Changes History.

When you expand on the item of interest, you can see the login that did the deed.

I hope your shop doesn't have everyone logging in as "sa"

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!

J39L4753 SSCrazy Points: 2803 More actions · Answer 5

Thanks so much for this I have now found who the culprit is 🙂

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

@lowell : you have shown great feature, thanks

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)