Error Creating Table-Valued Function

Question

Post reply

Error Creating Table-Valued Function

Stamey

SSCertifiable

Points: 5600
More actions
February 10, 2011 at 7:04 am

#245097

I have a Table-Valued function I am creating, or attempting to create. There seems to be a problem with a DB option/permission setting causing me not to be able to create it, because when I try to create it in the AdventureWorks DB it creates just fine, but I get the following error when trying to create it in my prod DB.
Error:
Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
The statement has been terminated.
I can't even get to the point where I am worried about permissions since I can't even create the function.
I copied a function from a Technet page, http://technet.microsoft.com/en-us/library/ms191165.aspx, and it creates fine in the AdventureWorks DB but not in my prod DB.
Here is the MS example from the page mentioned before, with most of the guts stripped out, and it still produces the same error in my prod DB, but not in my AdventureWorks DB.
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
BEGIN
RETURN;
END;
GO
Above works perfectly in the Adventureworks DB, but not in my prod DB.
All of this is taking place on my local WS, with SQL2008 Developer Edition, and I am SysAdmin on the box.
Any ideas as to what option/permission setting I need to look at?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Are you also including a GRANT statement when you try to create the function?

That error message would indicate that you are trying to grant EXECUTE permission to the function, but it returns a table, so you should be granting SELECT permission.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I would check for database/server level triggers - my guess is you have one that is misbehaving...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Stamey SSCertifiable Points: 5600 More actions · Answer 3

mister.magoo

You da man! It was a trigger the DBAs had put in the DB to automatically grant execute on items that were created by non-Sysadmins in the DB, but have not taken into account TVFs.

Thanks,

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Good news, glad you found it. And thanks for the feedback.:-)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);