Find out owners of objects


  • 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

    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,
    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
                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')
    from objects_cte cte
    inner join sys.database_principals dp
    on cte.principal_id = dp.principal_id
    where = 'YourUser';

    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

    Jumat, 13 Juli 2018 02.27