Search for owner of objects in databases

• SQL_Student

  SSCrazy

  Points: 2536

  More actions

  June 28, 2006 at 4:30 am

  #61282

  Hello People

  I'd like to know is it possible to search for the owner of objects in a database instead of going through it manually.

  I go a script that pick up a users logon in different databases, but id like to know for each object in that database

  Please assist

• Maverick100877

  SSCertifiable

  Points: 5240

  More actions

  June 28, 2006 at 4:37 am

  #646325

  hi

  I had got a script from here only where u need to put the login and u will get the objects owned by that login.

   

  set nocount on

  create table

  #owned (objectname varchar(500))

  exec

  sp_msforeachdb 'insert into #owned

  select ''[?].['' + RTRIM(su.name) + ''].['' + RTRIM(so.name) + '']'' as objectname

  from [?].dbo.sysobjects so inner join [?].dbo.sysusers su on su.uid = so.uid

  where su.sid = (select sid from master.dbo.syslogins where name = ''<login>'')'

  select

  * from #owned

  drop table

  #owned

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 28, 2006 at 4:48 am

  #646328

  Given that you only want to do this in one database, this should be simpler:

  use YourDB

  select name as Object, object_name(uid) as Owner from sysobjects

  John

• sree-226516

  SSCrazy

  Points: 2590

  More actions

  June 28, 2006 at 7:07 am

  #646345

  you can use  storeprocedure sp_helprotect to get object and object owner .

   

  Thanks,

  SR

  Thanks,
  SR

• SQL_Student

  SSCrazy

  Points: 2536

  More actions

  July 6, 2006 at 12:54 am

  #647680

  Thanks alot guys

  esp Kumar Singh ur sp works fine just needed to insert an @command1 after the SP thanks a mill

   

  set nocount on

  create table #owned (objectname varchar(500))

  set nocount on

  create table #owned (objectname varchar(500))

  exec sp_msforeachdb @command1= 'insert into #owned

  select ''[?].['' + RTRIM(su.name) + ''].['' + RTRIM(so.name) + '']'' as objectname

  from [?].dbo.sysobjects so inner join [?].dbo.sysusers su on su.uid = so.uid

  where su.sid = (select sid from master.dbo.syslogins where name = ''sa'')'

  select * from #owned

  drop table #owned

   

   

  You guys were great help