table owner

• Himansu Amin

  Ten Centuries

  Points: 1041

  More actions

  November 9, 2009 at 12:49 pm

  #139437

  Hello everyone,

  Is there anyway to figure out who the owners of tables are in a database instance of SQL Server 2005? I'm trying to clean up production tables, but I don't want to delete someone else's tables.

  Here what I use:

  select * from sysobjects where type = 'U'

  * Most users log on to sql server using Windows authentication.

  Let me know what you think.

  Thanks,

  Himansu

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  November 9, 2009 at 5:16 pm

  #1077042

  select u.name as UserName,o.name as ObjectName from sys.sysobjects o

  inner join sysusers u

  on u.uid = o.uid

  where [type] = 'u'

  This will get you the ObjectOwner

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Paul White

  SSC Guru

  Points: 150467

  More actions

  November 9, 2009 at 5:43 pm

  #1077046

  Another way...

  SELECT

  [user_name] = USER_NAME(CAST(OBJECTPROPERTYEX(T.[object_id], 'OwnerId') AS INTEGER)),

  [name]

  FROM

  sys.tables T

  ORDER BY

  [user_name],

  [name];

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Himansu Amin

  Ten Centuries

  Points: 1041

  More actions

  November 10, 2009 at 7:09 am

  #1077212

  Thanks everyone for your reponse.

  * I still get user_name = dbo for all the tables

  The users logon to the SQL Server with their Windows login. I guess there' s no such animal in SQL Server. It makes no sense. Even the ALPHAs have a DIR search by user.

  Thanks,

  Himansu