SQL Server 2008 – How to troubleshoot error : Granted or revoked privilege EXECUTE is not compatible with object.


Today while executing the following code i got this particular error message:

GRANT EXECUTE ON dbo.fn_SomeFunction_TVF TO SomeRole

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

I started to read about this error message and this is what i concluded.

You cannot grant execute permissions on Table-valued functions. This is the way this has been designed. Though i could not find as to why this is so.

Solution:
I had to use the references keyword. The syntax for the grant statement hence becomes:
GRANT REFERENCES ON dbo.fn_SomeFunction_TVF TO SomeRole.

A snapshot of the MSDN article that details out permission levels to different securables is below.

Grant

For more details please visit the msdn link :
http://msdn.microsoft.com/en-us/library/ms191291.aspx

Advertisements

Tagged: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: