TVP Permissions

Here’s just a short post to feed the search engine gerbils. So today, I created a stored procedure that accepted a list of ID’s and filtered them. The most efficient way to handle this was using table-valued parameters (TVPs). My code looks similiar to this:

USE AdventureWorks2012;
GO
 
/* Create our new table type */
IF NOT EXISTS(SELECT * FROM sys.types WHERE name = 'CustomerList')
BEGIN
    CREATE TYPE dbo.CustomerList AS TABLE 
    ( 
          CustomerID INT
        , ID         INT
    );
END;
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.CustomerTerritoryFilterGet_sp')
, N'IsProcedure') IS NOT NULL
    DROP PROCEDURE dbo.CustomerTerritoryFilterGet_sp;
GO
 
/* Create a proc to test with */
CREATE PROCEDURE dbo.CustomerTerritoryFilterGet_sp
      @Customers        CustomerList READONLY
    , @TerritoryFilter  INT
 
AS
BEGIN
 
    SELECT DISTINCT sc.CustomerID, c.ID
    FROM Sales.Customer AS sc
    JOIN @Customers AS c
        ON sc.CustomerID = c.CustomerID
    WHERE sc.TerritoryID = @TerritoryFilter;
 
    RETURN 0;
END
GO
 
/* Test our stored procedure */
DECLARE @myList CustomerList;
INSERT INTO @myList 
VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (6, 60), (7, 70);
 
EXECUTE dbo.CustomerTerritoryFilterGet_sp
      @Customers        = @myList
    , @TerritoryFilter  = 1;
GO
 
GRANT EXECUTE ON dbo.CustomerTerritoryFilterGet_sp To myApp;
GO

This would typically be sufficient for most stored procedures. However, my app dev was getting the following error:

The EXECUTE permission was denied on the object 'customerList', database 'AdventureWorks2012', schema 'dbo'.

Wait, we need to explicitly grant permissions to the new data type? Okay, that’s easy enough… let’s just run a quick GRANT statement:

GRANT EXECUTE ON dbo.CustomerList To myApp;
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'CustomerList', because it does not exist or you do not have permission.

Okay, now I’m confused… what am I missing? It turns out, you need to use a slightly different syntax to grant permissions to a new data type. This syntax, which explicitly tells SQL Server that we’re granting permissions on a Type class, works just fine:

GRANT EXECUTE ON TYPE::dbo.CustomerList To myApp;
 
/* Time to clean up! */
-- DROP PROC dbo.CustomerTerritoryFilterGet_sp;
-- DROP TYPE dbo.CustomerList;
Command(s) completed successfully.

You can find the full syntax here on Books Online:
http://msdn.microsoft.com/en-US/library/ms174346(v=SQL.90).aspx

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

3 Responses to TVP Permissions

  1. Alex Kuznetsov says:

    Michelle,

    We are creating all types in one separate schema, and all we needed to do is grant EXECUTE on that schema once, and we were all set.

    While we are at it, can you provide a scenario when this permission really helps, when a system would be less secure if everyone could use them by default? So far I don’t know such a scenario.

  2. That’s a great idea, Alex. I can’t think of a reason you wouldn’t want to do that, as long as you were isolating that schema to *only* data types.

  3. Andrea says:

    Very good script but I don’t understand why before I launch dba_indexDefrag_sp procedure and after launch it the query for check fragmentation give same result .. ?

    The select I used for check fragmentation is this:

    SELECT object_id AS ObjectID,
    index_id AS IndexID,
    avg_fragmentation_in_percent AS PercentFragment,
    fragment_count AS TotalFrags,
    avg_fragment_size_in_pages AS PagesPerFrag,
    page_count AS NumPages
    FROM sys.dm_db_index_physical_stats(DEFAULT, NULL, NULL, NULL , ‘DETAILED’)
    WHERE avg_fragmentation_in_percent > 30
    ORDER BY ObjectID, IndexID

    thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>