Using RAISERROR to indicate ACCESS DENIED error

Question

I have a sproc that verifies whether the caller is authorized to call this sproc with given params. In case when the caller is not authorized, I want to RAISERROR with ACCESS DENIED error number. Now, it cannot be error number 10011 (ACCESS DENIED in sys.messages), because the error number has to be greater than 50000. I don't want to add the same messages (english and localized texts as well) using sp_addmessage sproc just to recreate the same ACCESS DENIED error. What's the right way to do this?

EDIT: since required SQL code was supposed to be compatible with SQL Azure, I ended up calling RAISERROR with no error number and checking for default 50,000 error code in code behind (in SQL Azure there is currently no support for sp_addmessage and sys.messages).

Answer 1

You can't do this as what you want to raise is not an Access denied error, but rather an Access denied with these parameters error. Hence you need to create a custom error and raise that.

If you want the "real" access denied error it is a matter of revoking exec rights on the procedure.