SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Grant Execute on a function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 05/20/2005 :  12:44:49  Show Profile  Reply with Quote
Ok, I have written half a billion functions over the years (actual count might be closer to 80). I have yet to encounter this error message.

Server: Msg 4606, Level 16, State 1, Line 2
Granted or revoked privilege EXECUTE is not compatible with object.

Here is the function:

CREATE FUNCTION dbo.funAjProviders_asTable()

RETURNS @tmpProviderList TABLE
(
intProviderID int,
txtProviderName varchar(50)
)

AS
BEGIN


--GIVE US Aj FACILITIES THAT ARE MARKED AS INTERNAL PROVIDERS
INSERT INTO @tmpProviderList
SELECT
intProviderID,
txtProviderName

FROM tblProvider
WHERE txtProviderName LIKE '%Aj%'
AND intInternalProvider = 1



--GIVE US Aj FAMILIES PROVIDERS THAT ARE MARKED AS ACCEPTING REFERRALS
INSERT INTO @tmpProviderList
SELECT
intProviderID,
txtProviderName

FROM tblProvider
WHERE txtProviderName LIKE '%Families%'
AND intAcceptReferral = 1

RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[funAjProviders_asTable] TO [AjApp]
GO

GRANT EXECUTE ON [dbo].[funAjProviders_asTable] TO [AjReport]
GO


I can't see why this function would not allow EXECUTE permissions to be set on it. Am I just blind?

Thanks!

Aj

ajthepoolman
Constraint Violating Yak Guru

USA
384 Posts

Posted - 05/20/2005 :  14:17:30  Show Profile  Reply with Quote
ARGH!!!!

STUPID STUPID STUPID!!!!!!!!!!!!!

You can't grant EXECUTE on a function that returns a flippin' TABLE!!!

I will grant SELECT on the function and the troubles will be gone.

Aj
Go to Top of Page

sqlghost
Starting Member

23 Posts

Posted - 10/18/2006 :  09:33:10  Show Profile  Visit sqlghost's Homepage  Reply with Quote
Count me in as stupid..
-sp
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000