How to list objects that a user owns

• Rowshambo

  SSC Enthusiast

  Points: 178

  More actions

  May 5, 2006 at 2:58 am

  #168031

  Heya

  I've inherited a couple of SQL 2000 VLDB's (100G+) that have been very much neglectied in admin terms. The previous DBA that was here had a nasty habit of created objects in his username , everything from Agent jobs to UDF's (which means that we can't disable his account, lest everything falls over) and I have been looking at changing the ownerships back to sa. So the million dollar questions are, how can I :

  a) quickly get a list of all the objects that a specific sql user owns?

  b) set those objects to sa (also, with as little pain as possible)

  Thanks

• Tom Goltl

  SSCertifiable

  Points: 5815

  More actions

  May 5, 2006 at 7:34 am

  #636220

  Here's a script you can run in each database that will list out the objects each user owns.

  select b.name,a.name from sysobjects a join sysusers b

  on a.uid = b.uid

  where b.name = 'yourusernamehere'

  I would build a string based on this to use sp_changeobjectowner.  Something like:

  select 'sp_changeobjectowner '+char(39)+a.name+char(39)+','+char(39)+'newowner'+char(39) from sysobjects a join sysusers b

  on a.uid = b.uid

  where b.name = 'yourusernamehere'

  I would also query the syscomments table in each database to see where/if any of the objects that he owned are being referenced in stored procedures.

  Undoubtedly, you will break things when you do this.  Just be ready to fix them.

  Good luck

  Tom

• Rowshambo

  SSC Enthusiast

  Points: 178

  More actions

  May 5, 2006 at 8:58 am

  #636275

  Much Obliged