CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Jeppen
Jeppen

Reputation: 424

View permissions on table type via T-SQL

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

Answers (1)

Aaron Bertrand
Aaron Bertrand

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

Related Questions