How to know what user created a table?

Question

Post reply

How to know what user created a table?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
August 27, 2010 at 9:12 am

#148550

Hello all,
Is there a way to obtain the name of the user that create a table? I know I can create a trigger or something like that to capture that kind of info, But I want to know if there is native way to do so...

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You should be able to find that information in the default trace.

Check SQL Server 2005 Books Online for information on the default trace.

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 2

Another way? that db was moved from a now defunct server...

Lowell SSC Guru Points: 323518 More actions · Answer 3

if it's not in the default trace, which onyl keeps track of "whodunnit" information on DDL stuff for a limited time, and you do not have any other traces in place, there is no way to find out;

SQL does not keep that information, just the create_date and modify_date

select name,create_date,modify_date from sys.objects

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!

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Lowell (8/27/2010)
select name,create_date,modify_date from sys.objects

In the above we can get only the modification date and time but we cant get from which system/user the table has been modified/created.

I dont think other than trigger it can be cccomplished.

Thanks

Parthi

Thanks
Parthi

WayneS SSC Guru Points: 95460 More actions · Answer 5

To track this information going forward, you could put in a database level trigger.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 6

I have a db trigger for that, but those tables where create prior that trigger

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

Check the default trace: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/