Security audit for SQL public role

21 April,2015 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Security audit for SQL public role


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer