如何列出使用者定義的表類型的權限？

我正在使用一些使用者定義的表類型。它們非常有用。

您可以通過執行這個簡單的腳本來查看有關它們的資訊：

SELECT o.* from sys.table_types o

我有一個程序可以顯示使用者對象的所有權限。我也可以指定對象的名稱。程式碼是這樣的：

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

在我上面的程式碼中，我沒有列出我的使用者定義表類型的權限。

我怎樣才能做到這一點？

這些是我授予我的類型的權限，以便使用者可以使用它：

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 [WebDevelopment]
GO
GRANT EXECUTE ON TYPE::[dbo].[DespatchStatus] TO [WebDevelopment] AS [dbo]
GO

這就是它在儲存過程中的使用方式：

--=====================================================
-- declare and populate the DespatchStatus
--=====================================================
DECLARE @DIS DespatchStatus;
INSERT INTO @DIS(lngDespatchStatusID) VALUES (7)
INSERT INTO @DIS(lngDespatchStatusID) VALUES (11)
INSERT INTO @DIS(lngDespatchStatusID) VALUES (17)
INSERT INTO @DIS(lngDespatchStatusID) VALUES (19)

最後，受 Aaron 回答的啟發，我的許可腳本如下：（使用 UNION ALL）

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

                      UNION ALL

                       SELECT
                           dp.NAME AS principal_name
                               ,dp.type_desc AS principal_type_desc
                               ,o.NAME AS object_name
                               ,[type_desc] = 'User-Defined Table Type' 
                               ,p.permission_name
                               ,p.state_desc AS permission_state_desc
                           FROM sys.table_types  o
                               INNER JOIN sys.database_permissions  p ON o.user_type_id=p.major_id
                               LEFT OUTER JOIN sys.database_principals  dp ON p.grantee_principal_id = dp.principal_id
                          WHERE o.NAME like @OBJ

       )


       SELECT * FROM RADHARANI
       SELECT @@ROWCOUNT

正如您在下圖中看到的那樣，在所有對象之後，我的表格類型顯示了它的權限。

這將列出對錶類型顯式授予的權限，但不會列出通過角色或組成員身份隱式授予的權限，或針對包含架構授予的權限。

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

我很好奇您在系統中為表類型使用了什麼類型的顯式權限？從文件來看，您不需要為標準執行時查詢支持實現很多東西（SELECT例如，您不能直接授予）。似乎這主要用於元數據/控制。

引用自：https://dba.stackexchange.com/questions/83735