Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 9 down vote accepted

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

Your Answer

 
discard

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.