SQL Server Security Articles and Software - SQLNetHub

The “Public” Database Role in SQL Server

by

The “Public” Database Role in SQL Server is a special database role that is given by default to all logins. You cannot remove a login’s membership to this role because this behavior is built into SQL Server.

 

Article on SQLNetHub - The "Public" Database Role in SQL Server

What does the “Public” database role does in SQL Server?

Should we make use of it or at the end of the day just ignore it?

When you create a new login (and consequently a new database user) in SQL Server and do not give it any permissions anywhere (that is securable objects), then this database user inherits the permissions the public database role has.

So, let’s see an example. On a SQL Server 2014 instance I have just created a login named ‘testLogin’ (not quite  unique, isn’t it? 🙂 and just granted access to the ‘Adventureworks2014’ database without doing anything else. As you can see from the below screenshot, my login was just granted the Public database role on the database.

Article on SQLNetHub - The "Public" Database Role in SQL Server

 

The question now is what permissions the ‘testLogin’ inherited on AdventureWorks2014? I have not provided any permission so it’s just the permissions provided by the Public database role.

If we check the securableS for the Pulbic database role in AdventureWorks2014 security, we can see that the Public database role is granted with SELECT access to certain system catalog views such as:

    • sys.all_columns
    • sys.all_objects
    • sys.all_parameters
    • sys.all_sql_modules
    • sys.all_views
    • sys.allocation_units
    • sys.assemblies
    • etc.

 

 

Security recommendation about the “Public” database role in SQL Server

Even though the Public database role is granted by default SELECT permissions to certain system catalog views, you should never, ever add more permissions to this role. Just leave it as it is. If you make the mistake and add more permissions to the public database role then this will mean that any login that will be granted access to the database it will inherit all these permissions. So please, don’t do that!

 

Let’s try again to rephrase this with even more plain words: any permissions the Public database role has, are automatically granted to all database users. That’s why you should never add permissions to this role.
By the way, if you want to check if you have accidentally added more permissions to this role, you can try the below query:
USE [Database Name];
GO

SELECT 
DB_NAME() as DBName,
pm.[permission_name] as PermissionName,
ob.[name] as ObjectName,
pm.class_desc as ObjectType,
ob.type_desc as TypeDescription
FROM sys.database_permissions pm
INNER JOIN sys.database_principals pr ON pm.grantee_principal_id= pr.principal_id
LEFT JOIN sys.objects ob ON pm.[major_id] = ob.[object_id]
WHERE [state]='G' and major_id>=0 and pr.[name]='public' and ob.[name] IS NOT NULL;
GO

 

If the above query return any results, you should revise the permissions granted to the Public database role for the specific database.

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Secure your SQL Server Instances with DBA Security Advisor

DBA Security Advisor, is our SQL Server security tool, which can help you assess your SQL Server instances against a rich set of security checks. The assessment report, includes recommendations and remediation scripts that can help you better secure your SQL Server instances and databases (learn more…).

SQL Server Security Tool - DBA Security Advisor by SQLNetHub

Try DBA Security Advisor free for 30 days!

 

Featured Online Courses:

 

Other SQL Server Security-Related Articles

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)

Loading... 

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



Views: 16,829