Asked by:

revoking all permissions from public role in user database throws error when trying to access from vba front end

Question

As per audit requirement we were asked to remove all permissions from public role and assign these permissions to new user role. Hence we revoked all permissions from PUBLIC and assigned these permissions to a new user role.

this was tested in development and PAT environment. The users with new user role were able to access the database through VBA client in DEV and PAT. But when we tried implementing this in production, the application throws error ' Access denied on table
'tbl1'. Can anybody provide a solution for this.

We could not replicate the issue in development environment. Please provide us with a solution on this. Thanks in advance.

We could not replicate the issue in development environment. Please provide us with a solution on this. Thanks in advance.

At the risk of stating the obvious, the inability to replicate the issue in dev suggests permissions are different. If you are certain the permissions assigned to the role are the same and role membership is identical, check the user(s) membership of
other roles, especially fixed database and server roles. Permissions are cumulative so perhaps additional role memberships provide more permissions in dev that prod.