Security audit for SQL public role

Create a regular security audit on the SQL Server public role. The public role exists in every user database and by default every login is a member of the public role. If object privileges are assigned to the public role , it means every login will have access to those object privileges.

It’s a common myth , the public role is a fixed server role. It’s implemented differently. Object level permissions  can be  applied using GRANT,DENY,REVOKE on the public role.

Avoid applying object level permissions to the public role, unless there is a very specific application requirement.

For example , if an object level permission was applied to a  table for public , such as :

GRANT SELECT ON OBJECT::dbo.test TO public;

Every login would have SELECT permissions to this table automatically, unless an explicit REVOKE or DENY was applied. It is easy to see how this is a dangerous practise.

REVOKE SELECT ON OBJECT::dbo.test TO public;
DENY SELECT ON OBJECT::dbo.test TO public;

Read more about SQL Server Security Policy

 Script to check public role object level permissions

  select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
,perm.major_id
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
WHERE princ.name = 'public'
AND perm.major_id > 0

Read more on security

Powershell sql server security audit
Stored Procedure Checklist

SQL Server – How to manage Failed Logon attempts