16

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?

22

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
  • 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 '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? – PseudoToad Apr 9 '15 at 11:40
3

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
  • 1
    Question asked for every database object and not just the database itself. – SqlWorldWide Oct 26 '17 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.) – youcantryreachingme Sep 17 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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