Link to home
Start Free TrialLog in
Avatar of usky1
usky1

asked on

who created a sql table

Is there a way to find out who created a sql table? We have tables that were created over the past 5 years all owned by dbo. Can I find any more information on the creation?
Avatar of chandrasekar1
chandrasekar1
Flag of India image

SELECT o.object_id, o.name,

CASE WHEN principal_id is NOT NULL THEN (SELECT name FROM sys.database_principals dp WHERE dp.principal_id=o.principal_id)

ELSE (SELECT dp.name FROM sys.database_principals dp,sys.schemas s WHERE s.schema_id=o.schema_id and s.principal_id=dp.principal_id)

END as Owner

FROM sys.objects o

WHERE type='U'

Open in new window


src link
http://www.sqlservercentral.com/Forums/Topic324773-149-1.aspx#bm324899
Avatar of _agx_
.. won't that just return 'dbo' ?
Avatar of usky1
usky1

ASKER

agx - that is all it does return. Do you have any suggestions?
Sorry, no. I'm not sure the system tables store even store additional info. Beyond the owner, creation/modification that is. Especially if they were created by a sql login like "sa" or something.  
- no, you probably can't. there is a method to get such information from the transaction log but not sure if its still hold the 5 years back log:

http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
Just confirming that there is no way. "dbo" is always replacing real user's info, even if Windows Authentication is used. Only if there is no mapping to dba, the user is maintained as schema owner - and again, nowhere else.
ASKER CERTIFIED SOLUTION
Avatar of DevDen
DevDen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of usky1

ASKER

Sorry about the delay in responding.

The article is what i was looking for.   Thanks