SQL Copilot
-- Shows schemas the given database user owns or has permissions on (direct or via roles)DECLARE @user sysname = 'ei'; -- change to the target user;WITH UserDP AS ( SELECT principal_id FROM sys.database_principals WHERE name = @user),UserRoles AS ( SELECT role_principal_id FROM sys.database_role_members WHERE member_principal_id = (SELECT principal_id FROM UserDP))-- Schemas owned by the user or by a role the user is a member ofSELECT s.name AS schema_name, 'Owner' AS access_type, NULL AS permission_name, NULL AS permission_stateFROM sys.schemas sWHERE s.principal_id = (SELECT principal_id FROM UserDP) OR s.principal_id IN (SELECT role_principal_id FROM UserRoles)UNION-- Schemas with explicit permissions granted to the user or to a role the user is a member ofSELECT s.name AS schema_name, 'Permission' AS access_type, p.permission_name, p.state_descFROM sys.database_permissions pJOIN sys.schemas s ON p.class = 3 -- class = 3 => SCHEMA AND p.major_id = s.schema_idWHERE p.grantee_principal_id = (SELECT principal_id FROM UserDP) OR p.grantee_principal_id IN (SELECT role_principal_id FROM UserRoles)ORDER BY schema_name, access_type;