As per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role. This is because public role is a special database role that exists in every user database, and by default, every database user is automatically assigned to this built-in role. This role is similar to Windows NT Everyone group, for example, if you grant privileges to this role, then all members of this role automatically get’s this permission. Due to this reason, when locking down access controls, then, we need to look at each individual user’s privileges as well as the privileges assigned to public role.

You can use the following query, which lists all privileges that has been granted to public database role in the specified database:

USE [<Database_Name>] -- Specify database name
GO
WITH [PublicRoleDBPermissions]
AS
(SELECT p.[state_desc] AS [PermissionType]
       ,p.[permission_name] AS [PermissionName]
       ,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole]
       ,CASE p.[class]
          WHEN 0 THEN 'Database::' + DB_NAME()
          WHEN 1 THEN OBJECT_NAME(major_id)
          WHEN 3 THEN 'Schema::' + SCHEMA_NAME(p.[major_id]) 
        END AS [ObjectName]
 FROM [sys].[database_permissions] p
 WHERE p.[class] IN (0, 1, 3)
        AND p.[minor_id] = 0)
SELECT [PermissionType]
      ,[PermissionName]
      ,[DatabaseRole]
      ,SCHEMA_NAME(o.[schema_id]) AS [ObjectSchema]
      ,[ObjectName]
      ,o.[type_desc] AS [ObjectType]
      ,[PermissionType] 
            + ' ' + [PermissionName] 
            + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
            + '.' + QUOTENAME([ObjectName]) 
            + ' TO ' + QUOTENAME([DatabaseRole]) AS [GrantPermissionTSQL]
      ,'REVOKE'
            + ' ' + [PermissionName] 
            + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
            + '.' + QUOTENAME([ObjectName]) 
            + ' TO ' + QUOTENAME([DatabaseRole]) AS [RevokePermissionTSQL]
FROM [PublicRoleDBPermissions] p
JOIN [sys].[objects] o
    ON o.[name] = p.[ObjectName]
AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0
WHERE [DatabaseRole] = 'Public'
ORDER BY [DatabaseRole], [ObjectName], [ObjectType]

This query is written using following two system catalogs: sys.database_permissions and sys.objects, and returns the following columns:

• PermissionType – Returns the type of granted permission.
• PermissionName – Returns the name of permission.
• DatabaseRole – Returns the public role name.
• ObjectSchema – Returns the schema of database securable, to which the securable belongs.
• ObjectName – Returns the name of the database securable.
• ObjectType – Returns the type of database object.
• GrantPermissionTSQL – Returns the statements to grant public database role access on all database objects. Note: The results of this statement are used for rollback purposes.
• RevokePermissionTSQL – Returns statements to revoke public database role access from all database objects, where it has permissions.

Sample Ouput

If this query returns rows, this means that unnecessary privileges have been granted to public role. To remove these unnecessary privileges, create a user defined database role, and grant this user define database role same permissions that has been granted to public role. Once done, add required database users to this role, and after that, revoke the permissions assigned to public database roles.

For more information about SQL Server Server-Level and Database-Level roles, see Server-Level Roles and Database-Level Roles.