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
