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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Grant Execute on a function

Author  Topic 

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-05-20 : 12:44:49
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

384 Posts

Posted - 2005-05-20 : 14:17:30
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 - 2006-10-18 : 09:33:10
Count me in as stupid..
-sp
Go to Top of Page
   

- Advertisement -