Default Permissions in SQL Server Public Role - permission not granted

What is the SQL server public role?

In an SQL Server, roles act like security groups that control what users can do within the database environment. The roles designate the access groups determining who can access specific databases and what they can do with the data within those databases.

The public role is a special database role that everyone is assigned by default when they become a member of a database. By default, the public role has very limited permissions, often no permissions at all. This means users need to be assigned specific roles with appropriate permissions to do anything within a database.

SQL CIS Benchmark Guide

The difference between server roles and database roles

When assigning roles on an SQL server there are two different levels of roles to consider, server level and database level. Server roles manage overall SQL Server access and all the databases within, while database roles control access to specific databases within the server and the data it contains.

The table below shows the scope, permissions and examples of what each role can do:

Benefits of using roles

Roles are imperative to the functioning of an SQL server and its databases. They improve security by allowing for granular control over user access, abiding by the principle of least privilege to minimize the risk of unauthorized data modification or breaches. By grouping users with similar needs into roles makes it easier and simple to manage permissions for large numbers of users. This reduces complexity by avoiding the need to assign permissions directly to individual users for each database object, streamlining administration.

Why keep default permissions for public server roles

Unlike other roles, the public role is assigned by default and cannot be unassigned, therefore any permission changes granted to the public role will affect all users unless individually specified. For these reasons it is important to be careful what permissions are granted to this role as all users, including future users will be able to access the database.

This also applies the opposite way, if permissions are revoked, all users will lose access to the data unless permission is explicitly granted on either an individual level or to user defined server roles.

How to check what permissions are assigned to a public role

To check what permissions have been granted or revoked from the public role in an SQL server use the following syntax:

This query should not return any rows.

Remediation

Add the extraneous permissions found in the Audit query results to the specific

logins to user-defined server roles which require the access.

2. Revoke the <permission_name> from the public role as shown below:

Default value

By default, the public server role is granted VIEW ANY DATABASE permission and the CONNECT permission on the default endpoints (TSQL Local Machine, TSQL Named Pipes, TSQL Default TCP, TSQL Default VIA). The VIEW ANY DATABASE permission allows all logins to see database metadata, unless explicitly denied.

Recommended setting

Don’t grant server permissions to the server public role.

Important: After setup completes the PUBLIC role has CONNECT permission on all the endpoints except the Dedicated Admin Connection. This is normal and should not be normally changed. (Access is controlled by using the CONNECT SQL permission which is automatically granted when new logins are created.)

Hardening and best practices 

While meticulously configuring SQL Server permissions and security settings is crucial, server hardening offers a broader approach for enhanced database security by employing a layered defense strategy. This approach not only minimizes attack surfaces by disabling unnecessary services, but also simplifies security management.