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?

3 Answers 3


This should get you what you're looking for:

;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 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
  • 1
    (Found this through searching.) This doesn't include non-schema-scoped objects such as Service Broker message types. Do you know of an easy way to get that information without digging into all of the specific object metadata views? (Also, I'm not sure why you limited the types of objects returned in this query, as it could exclude some objects of interest.)
    – Jon Seigel
    Nov 4, 2013 at 19:58
  • Well, I ended up creating my own view. If you know of a better solution, please let me know.
    – Jon Seigel
    Nov 4, 2013 at 22:37
  • 1
    @JonSeigel I have written one using a UNION to the above. Can you post yours so I can compare and improve mine?
    – PseudoToad
    Apr 9, 2015 at 11:40

To show all non-sa database owners:

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

If you need SQL system Job owners:

select s.name,l.name  
from  msdb..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
  • 2
    Question asked for every database object and not just the database itself. Oct 26, 2017 at 1:12
  • @SqlWorldWide - but the accepted answer does not include databases owned, so this answer is additionally helpful. (Interestingly, the 'Owner' names returned here do not even exist in the sys.database_principals table referenced in the accepted answer. Would love to know what's going on.) Sep 17, 2019 at 4:33

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.