Reputation: 424
I've been looking everywhere but still haven't found the answer. Via T-SQL I'm querying the database to get the "user-definded table types".
Using the query found on this forum I'm able to see them
select *
from sys.types tp
where is_user_defined = 1
What I want to get is the permissions linked to this object. Could anyone help me please?
Upvotes: 0
Views: 156
Reputation: 280644
This will return explicitly defined permissions (e.g. GRANT EXEC ON TYPE::dbo.mytype TO public;
), but not those implicitly granted (e.g. to members of a role or group).
SELECT [type] = QUOTENAME(s.name) + '.' + QUOTENAME(tp.name),
dp.[permission_name],
[principal] = p.name
FROM sys.types AS tp
INNER JOIN sys.schemas AS s
ON tp.[schema_id] = s.[schema_id]
INNER JOIN sys.database_permissions AS dp
ON tp.user_type_id = dp.major_id
AND dp.class_desc = N'TYPE'
INNER JOIN sys.database_principals AS p
ON p.principal_id = dp.grantee_principal_id
WHERE tp.is_user_defined = 1;
Upvotes: 1