I have a stored procedure that has EXECUTE AS another user that has very restricted access. I cannot get the results from OBJECT_NAME(@@PROCID) when I run that stored procedure. @@PROCID does have a value, so it's the lookup that is failing.
I think it's a permissions issue, so can someone tell me where the name of the stored procedure is obtained from? Maybe I can fix my issue by granting some SELECT permissions to the EXECUTE AS user.
If anyone has any other ideas, LMK. I'm hardcoding the stored procedure name for now.
From the documentation for OBJECT_ID:
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object.
Also further reading will also tell you that you need to grant the user any permission on the object (i.e. SELECT, EXECUTE etc.)