Grant Execute on a function - SQL Server Forums

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

sqlghost
Starting Member

23 Posts

Posted - 2006-10-18 : 09:33:10

Count me in as stupid..
-sp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources