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

    Question

  • Hi,

    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.

    Tuesday, July 02, 2013 8:20 AM

All replies

  • Why revoke from public role? IIRW I have read a document to state that we should not revoke perm from public role.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 02, 2013 8:53 AM
  • Hi Uri,

    Thanks for your reply. But as per the audit requirement we are asked to remove permissions from public role in a user database.

    Tuesday, July 02, 2013 10:58 AM
  • 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.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, July 02, 2013 11:10 AM
  • Is it possible you executed a DENY of the permissions instead of REVOKE of the permissions. Check sys.server_permissions and sys.database_permissions.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 02, 2013 3:32 PM