Question :
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 [WebDevelopment]
GO
GRANT EXECUTE ON TYPE::[dbo].[DespatchStatus] TO [WebDevelopment] AS [dbo]
GO
And this is how it is used inside stored procedures:
--=====================================================
-- 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)
At the end, inspired by Aaron’s answer my permission script became as follows:
(using 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
and as you can see on the picture below, my table type is displayed with its permissions, after all the objects.
Answer :
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.