How to find owner of the table?

Question

How to find owner of the table?

dva2007

SSCertifiable

Points: 7707
More actions
November 18, 2011 at 4:23 am

#263151

I was wondering how can I check who has created the table in sql server 2005? Right Click -Properties doesnt give much info. Is there any other built in method to check?

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

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Check the default SQL Server trace file (in your SQL Server's default installation directory, in the LOG folder), all DDL's are tracked there.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

#1 Ideally, check in the change management script system.

#2 See #1 if #1 fails :hehe:.

No seriously, other than asking around (assuming the default trace has been rolled over), you can try tracking in what sp or views that table is used and try to tie that to a project & it's owner.

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

thanks for the reply, i am not aware of all DBA side activity and just writes t-sql all the time. We dont even have enough access to look into the installation directory. If it is the case of looking into sql installation directory then i need to raise this with DBA. I thought if we have some way of looking the owner via script (the same way we look for the the existing procedure running on server, time taken etc.).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If the person creating the object has dbo rights (so any database owner or sysadmin), ownership will be dbo (principal_id = NULL in sys.objects)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dva2007 SSCertifiable Points: 7707 More actions · Answer 5

thanks gail, i already looked into sys.objects but couldnt find any thing like the owner of the table. As you said prinical_id is null.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

There is a standard report delivered with SSMS call Schema Changes History Report. Run that report and if the information still exists in the default trace it will show up and you can see who created the table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs