none
Find out owners of objects

    Pertanyaan

  • Dear All,

    I'm trying to find out if there is a way of finding out if a particular login is an owner of Objects in sql so they could be removed after transferring their ownership please?

    Thanks in advance!

    Kamis, 12 Juli 2018 08.11

Semua Balasan

  • Hello,

    Logins can only own database, but not database objects

    select pr.name, db.name
    from sys.databases as db
        inner join
        sys.server_principals as pr
            on db.owner_sid = pr.sid


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Kamis, 12 Juli 2018 08.21

  • Is there a way of finding out schema owners?

    T‌hank you
    Kamis, 12 Juli 2018 09.43
  • select sc.name, pr.name
    from sys.schemas as sc
         inner join
         sys.database_principals as pr
             on sc.principal_id = pr.principal_id



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Kamis, 12 Juli 2018 14.34
  • Hi,

    Check this, hope it helps:

    ;with objects_cte as
    (
        select
            o.name,
            o.type_desc,
            case
                when o.principal_id is null then s.principal_id
                else o.principal_id
            end as principal_id
        from sys.objects o
        inner join sys.schemas s
        on o.schema_id = s.schema_id
        where o.is_ms_shipped = 0
        and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
    )
    select
        cte.name,
        cte.type_desc,
        dp.name
    from objects_cte cte
    inner join sys.database_principals dp
    on cte.principal_id = dp.principal_id
    where dp.name = 'YourUser';
    

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Jumat, 13 Juli 2018 02.27
    Moderator