Can I retrieve all database objects owned by a particular user?

Question

We have a user who is leaving and I need to know every database object that he owns. Is there a query that will provide this information?

Answer 1

This should get you what you're looking for:

;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';

This will get objects that are owned by your particular user (substitute 'YourUser' of course). The types of objects this query pulls are:

• FN = SQL scalar function
• FS = Assembly (CLR) scalar-function
• FT = Assembly (CLR) table-valued function
• IF = SQL inline table-valued function
• P = SQL Stored Procedure
• PC = Assembly (CLR) stored-procedure
• TA = Assembly (CLR) DML trigger
• TF = SQL table-valued-function
• TR = SQL DML trigger
• U = Table (user-defined)
• V = View

Answer 2

For Jobs, you cannot use syslogins as the owner may be part of a group and not exist in logins. Use the below

select msdb.[dbo].[SQLAGENT_SUSER_SNAME](owner_sid), * 
from msdb.dbo.sysjobs

Answer 3

To show all non-sa database owners:

SELECT OwnerID = suser_sname( owner_sid )
    ,*
FROM sys.databases
WHERE suser_sname( owner_sid ) <> 'sa'

If you need SQL system Job owners:

select s.name,l.name  
from msdb.dbo.sysjobs s 
left join master.sys.syslogins l
    on s.owner_sid = l.sid 
where l.name is not null
  and l.name <> 'sa'
order by l.name