In my RBAC model, the middle-tier needs to check permissions before displaying certain UI elements. This permission need to be further enforced in the relevant stored procedure.

For an example, the "ban member" button visibility depends on whether you have the "ban member" permission. The "ban member" stored procedure also needs to check that the current user can actually ban members.

I wrote a stored procedure "HasPermission 'ban_member', @current_userid" stored procedure which both the relevant SP and the UI can use. However, considering that this type of functionality is required on a lot of elements/features throughout the application, this SP will need to be called several times (like ban button, post button, edit button etc).

Should I just let SQL Server handle it OR should I create an application layer cache that will cache the 'highest' permissions of the user? I know SQL Server can handle millions of transactions so not sure whether to cache or not.