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?
Add a comment |
3 Answers
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– Jon Seigel2013-11-04 19:58:53 +00:00Commented 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– Jon Seigel2013-11-04 22:37:50 +00:00Commented 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– PseudoToad2015-04-09 11:40:39 +00:00Commented Apr 9, 2015 at 11:40
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 
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 
- 2Question asked for every database object and not just the database itself.SqlWorldWide– SqlWorldWide2017-10-26 01:12:25 +00:00Commented 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.)youcantryreachingme– youcantryreachingme2019-09-17 04:33:06 +00:00Commented Sep 17, 2019 at 4:33