How to find all entities owned by a login?

I have to produce a report that lists all the SQL entities owned by a particular login.

Is there any way I can audit/get list of all the entities owned by a login using SQL audit/dmv?

For example: Currently I am looking into agent jobs (sysjobs), and in each databases owner property (sys.databases). Not sure what all places to look into to cover all entities. Any advice will be helpful.

A login can own a database, but a user owns schemas or schema-scoped objects, so you need to map users to logins.

The following script should get you all those database-level objects for a given principal. Schemas are by default owned by the database owner, and objects are by default owned by the schema owner, unless changed in which case this query will show them.

As far as sub-database-level data is concerned, you must specify the database, for example YourDatabase.sys.objects if you want all databases at the same time, do not forget database-level objects master and other system databases if necessary.

The full list of objects that can be owned is available on this page, but the general template goes like this:
DECLARE @id int = (SELECT dp.principal_id FROM sys.database_principals dp WHERE dp.name = 'UserName');

SELECT 'Schema', s.name
FROM sys.schemas s
WHERE s.principal_id = @id

UNION ALL

SELECT 'Object', o.name
FROM sys.objects o
WHERE o.principal_id = @id

SELECT 'Object', as.name
FROM sys.asymmetric_keys as
WHERE as.principal_id = @id

.....


Server logins can also own some server objects. You can do this in a similar fashion
DECLARE @id int;
DECLARE @sid varbinary(85);

SELECT
@id = sp.principal_id,
@sid = sp.sid
FROM sys.server_principals sp
WHERE sp.name = 'LoginName';

SELECT 'Database' AS ObjectType, d.name
FROM sys.databases d
WHERE d.owner_sid = @sid

UNION ALL

SELECT 'Assembly' AS ObjectType, a.name
FROM sys.assemblies a
WHERE as.principal_id = @id

.....


StackExchange Database Administrators Q#316747, answer score: 3