0

I have a SQL function that is only called from inside another stored procedure. I have granted EXECUTE rights for the stored procedure that calls the function.

Do I need to grant EXECUTE permissions to the function as well?

EDIT

In response to the comment from @AaronBertrand

The function does not access any other objects in the database. It is used to build a string.

Shai Cohen
  • 6,074
  • 4
  • 31
  • 54
  • @AaronBertrand: true, but the syntax is `GRANT EXECUTE ON ...`. That's what I meant. – Shai Cohen Mar 14 '13 at 00:02
  • Again, it depends on what the function accesses, the rights of the user calling the stored procedure, etc. Did you try it? What happened? – Aaron Bertrand Mar 14 '13 at 00:08
  • @AaronBertrand: I have updated my answer with more information that you requested. I dropped the function and recreated it (to remove any permissions that might have been associated with the function) and it seems to work. – Shai Cohen Mar 14 '13 at 00:32
  • @AaronBertrand: Do you want to put your comment as an answer so I can give you credit? I don't like having unanswered questions .... :) – Shai Cohen Mar 25 '13 at 16:47

1 Answers1

1

If the problem is that your user can't execute it, try dropping and re-creating the function, and applying the appropriate permissions on the function itself and any object(s) it references. There may be a DENY hanging around, which will always trump GRANT.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • It is also worth mentioning that in my specific case because the function does not access any other objects in the DB, no rights needs to be assigned. – Shai Cohen Mar 25 '13 at 17:23