The “Public” Database Role in SQL Server

The “Public” Database Role in SQL Server

The “Public” Database Role in SQL Server

In SQL Server, the “Public” database role 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.

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

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.

 

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

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.
The "Public" Database Role in SQL Server - Article on SQLNetHub

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.

 

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!

 

Other SQL Server Security-Related Articles

 

Check our latest software releases!

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

 

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

Loading... 

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

© SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.