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:
/* Create our new table type */
IF NOT EXISTS(SELECT * FROM sys.types WHERE name = 'CustomerList')
CREATE TYPE dbo.CustomerList AS TABLE
, ID INT
, N'IsProcedure') IS NOT NULL
DROP PROCEDURE dbo.CustomerTerritoryFilterGet_sp;
/* Create a proc to test with */
CREATE PROCEDURE dbo.CustomerTerritoryFilterGet_sp
@Customers CustomerList READONLY
, @TerritoryFilter INT
SELECT DISTINCT sc.CustomerID, c.ID
FROM Sales.Customer AS sc
JOIN @Customers AS c
ON sc.CustomerID = c.CustomerID
WHERE sc.TerritoryID = @TerritoryFilter;
/* 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);
@Customers = @myList
, @TerritoryFilter = 1;
GRANT EXECUTE ON dbo.CustomerTerritoryFilterGet_sp To myApp;
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:
Let’s get this blog thing started!
I sometimes find that appropriate permissions are not always assigned to stored procedures during creation. This usually occurs when a proc is scripted and re-deployed in a hurry, or when a non-DBA decides to play in my sandbox. I use this rather simple script to quickly find and grant all missing permissions.
Declare @schema_owner varchar(20);
Set @schema_owner = 'dbo';
Select [name] As 'storedProcedure', 'Grant Execute On '
+ @schema_owner + '.' + [name]
+ ' To [insertDatabaseRoleHere];' As 'sqlCode'
From sys.objects With (NoLock)
Where [name] Not In (
From sys.database_permissions p With (NoLock)
Inner Join sys.objects o With (NoLock)
On p.major_id = o.object_id
Inner Join sys.database_principals u With (NoLock)
On u.principal_id = p.grantee_principal_id
Where u.name = '[insertDatabaseRoleHere]'
And [type] = 'P';
This is especially useful for when you have a common database role that all stored procedures need to belong to; for example, infrastructures that share role membership to allow applications to execute procs.