4

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?

enter image description here

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.

enter image description here

4

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.

  • 3
    I have updated the question to show the permissions I grant to the table type. Without the EXECUTE, for example, they cannot run a stored procedure that declares a DespatchStatus. – Marcello Miorelli Nov 26 '14 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.