SQL SERVER – Public Role Permissions and Effective Security Risk

Every database user belongs to Public and can’t be removed from that role. A reader asked whether the role should be eliminated.

Review Public permissions in their database scope

SELECT p.state_desc, p.permission_name, p.class_desc,
       OBJECT_SCHEMA_NAME(p.major_id) AS ObjectSchema,
       OBJECT_NAME(p.major_id) AS ObjectName
FROM sys.database_permissions AS p
WHERE p.grantee_principal_id = DATABASE_PRINCIPAL_ID(N'public')
ORDER BY p.class_desc, p.permission_name;

A grant to public applies to every database user. Broader grants can expose data or allow changes. My earlier suggestion that public can’t change data was too broad.

Inspect explicit grants and application effective permissions. The query reports catalog-visible explicit permissions. It does not calculate every user effective permission or every implicit permission. Server-level public requires a separate scope review.

Supported role operations can’t eliminate database public membership. Review and correct grants instead. Use dedicated application roles and assess dependencies before revoking a shared permission.

Reference: Database public role behavior.

Follow a shared grant to its practical effect

Suppose an application only needs to read a reporting table. A permission assigned to a dedicated role can be limited to that application’s users. Assigning the same permission to the shared role changes the intended audience. Check which object is involved and whether everyone in the database should receive that access before choosing where to place the grant.

Read the catalog’s permission state and class together. The object-name columns in this example are meaningful for object permissions. A schema or database permission belongs to a different class, so those columns should not be treated as an object lookup for every returned row. Metadata visibility can also limit the rows that the inspecting account can see.

Next, review the application’s actual access under its own identity. Other role memberships, direct permissions and ownership can affect the result. A catalog list of explicit entries is useful evidence, but it is not a complete effective-permission calculation. Keep the shared grant review separate from that wider investigation.

Before removing a permission, identify the applications and ordinary operations that depend on it. Record the intended replacement, test the expected behavior and retain a way to restore the previous setting if the change has an unintended effect. Changing access requires more evidence than noticing a role name that every account shares.

Reference: Microsoft’s catalog permission and metadata-visibility documentation.

Related reading

• Comprehensive Database Performance Health Check

Public membership is not the whole security risk, it is shared membership whose granted permissions need review.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.