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.
For more details please visit the msdn link :
http://msdn.microsoft.com/en-us/library/ms191291.aspx
Applied SQL... 