How to list the permissions on User-Defined Table Types?

I am using some user-defined table types. They are very useful.

you can see information about them by running this simple script:

SELECT o.* from sys.table_types o

I have a procedure that shows me all the permissions on the user objects. I can specify the name of the object too.
the code is this:

DECLARE @OBJ SYSNAME
SELECT @OBJ ='%'  -- shows all objects 
;WITH
RADHARANI AS (

                SELECT
                    dp.NAME AS principal_name
                        ,dp.type_desc AS principal_type_desc
                        ,o.NAME AS object_name
                        ,o.type_desc
                        ,p.permission_name
                        ,p.state_desc AS permission_state_desc
                    FROM sys.all_objects  o
                        INNER JOIN sys.database_permissions  p ON o.OBJECT_ID=p.major_id
                        LEFT OUTER JOIN sys.database_principals  dp ON p.grantee_principal_id = dp.principal_id
                   WHERE O.OBJECT_ID > 0
                     AND O.TYPE <> 'S'    -- no system
                     AND O.parent_object_id = 0 -- no constraints
                     AND o.NAME like @OBJ

)

SELECT * FROM RADHARANI
SELECT @@ROWCOUNT

On my above code, I don't list the permissions on my User-Defined Table Types.

How can I achieve that?

These are the permissions I grant to my type so that the users can use it:

```
USE [Bocss2]
GO

--=====================================================
-- create the DespatchStatus table type
-- if it does not exist already
--=====================================================
IF NOT EXISTS (SELECT * from sys.table_types) BEGIN

CREATE TYPE [dbo].[DespatchStatus] AS TABLE(
lngDespatchStatusID int not null
,PRIMARY KEY CLUSTERED(lngDespatchStatusID)

)
END
GO
use [Bocss2]
GO
GRANT REFERENCES ON TYPE::[dbo].[DespatchStatus] TO [WebDevelopment]
GO
GRANT VIEW DEFINITION ON TYPE::[dbo].[DespatchStatus] TO [WebDevelopmen

This will list explicitly granted permissions on table types, but not those granted implicitly through role or group membership, or permissions granted against the containing schema.

SELECT 
  [schema] = s.name, 
  [type] = t.name, 
  [user] = u.name, 
  p.permission_name, 
  p.state_desc
FROM sys.database_permissions AS p
INNER JOIN sys.database_principals AS u
  ON p.grantee_principal_id = u.principal_id
INNER JOIN sys.types AS t
  ON p.major_id = t.user_type_id--.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
WHERE p.class = 6; -- TYPE

I am curious what type of explicit permissions you are using in your system for table types? From the documentation there isn't a whole lot you need to implement for standard runtime query support (you can't grant SELECT directly, for example). It seems this is mostly there for metadata / control.

StackExchange Database Administrators Q#83735, answer score: 5