Post reply

Search for owner of objects in databases

  • 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

  • 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

  • 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

  • June 28, 2006 at 7:07 am

    #646345

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

     

    Thanks,

    SR

    Thanks,
    SR

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

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