Find user who created table??

Hi,

We are using SQL Server 2005.

I urgently need to find a way to find out who created some tables in one of our databases.  Is this possible without purchasing some third party tool?

Thanks,

-ws
LVL 1
Westside2004Asked:
Who is Participating?
 
Anthony PerkinsCommented:
No.
0
 
Kevin CrossChief Technology OfficerCommented:
AFAIK, no, is correct.

You could setup auditing to do this in the future.
http://www.dotnetheaven.com/UploadFile/a.ferendeles/SQLAudit02272007111637AM/SQLAudit.aspx

In theory you could try to implement this methodology which is usually used for auditing activity on existing tables to look at the sysobjects table for insertions of records with xtype = 'U'.

Regards,
Kevin
0
 
Anthony PerkinsCommented:
Now that the horse has effectively escaped the barn you can close the door by implementing approriate permissions or if you are unable to do that than implement DDL Triggers.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
chapmandewCommented:
>>Is this possible without purchasing some third party tool
Absolutely.  Does it take preparation to do so?  Yes.  It is going to be possible w/o it....likely not.  The one glimmer of hope is the fact that SQL 2005 keeps track of a default trace to review if something happens to your server.  Off of the top of my head, I do not think it captures CREATE TABLE DDL statements, but it is worth a check to see.

At the end of the day, putting a properly designed security scheme in place (as the others have suggested) is the way to solve this problem.

One other thing that is worth nothing...red-gate.com has a product out there that is supposed to be similar to the lumigent log explorer for capturing statements through log backups...and I believe they have it free.  http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
0
 
Westside2004Author Commented:
Thanks!
0
 
Anthony PerkinsCommented:
>>red-gate.com has a product out there that is supposed to be similar to the lumigent log explorer <<
Unfortunately it is only compatible with SQL Server 2000.  Apparently they could not negotiate the appropriate license from Microsoft for SQL Server 2005.
0
 
chapmandewCommented:
Well that sucks.  I know there is a design document you can obtain from MS that gives you some sort of direction as to the internals for developing this kind of software.  But...you have to be an ISP to get it.  Not sure why the red-gate product hasn't upgraded their software for this...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.