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

create a table AccessDeniedTable that the current DB user will never have access to. when you hit your logical ACCESS DENIED condition issue a SELECT @x=COUNT(*) FROM AccessDeniedTable which should throw the actual error you are after.

IF @Accesslevel<5 ---your condition here
BEGIN
    SELECT @x=COUNT(*) FROM AccessDeniedTable --throw standard ACCESS DENIED error
    RAISERROR('FATAL ERROR',16,1) --just in case actual error is not thrown
    RETURN 999999 --just in case
END

Answer 2

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.