Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.11 seconds. Powered By: Snitz Forums 2000