Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 12 down vote accepted

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 = '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
share|improve this answer
(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 '13 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 '13 at 22:37
@JonSeigel I have written one using a UNION to the above. Can you post yours so I can compare and improve mine? – Gizmo Apr 9 '15 at 11:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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