Solved

Find user who created table??

Posted on 2008-10-04
7
255 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Westside2004
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
Comment Utility
No.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
Comment Utility
>>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
 
LVL 1

Author Closing Comment

by:Westside2004
Comment Utility
Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now