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.
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 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:
Security recommendation about the “Public” database role in SQL Server
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
Learn more tips like this! Get the Online Course!
Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
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!
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…).Try DBA Security Advisor free for 30 days!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Other SQL Server Security-Related Articles
- Essential SQL Sever Administration Tips
- Why You Need to Secure Your SQL Server Instances
- SQL Server Row Level Security by Example
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- Policy-Based Management in SQL Server
- Frequent Password Expiration: Time to Revise it?
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- 10 Facts About SQL Server Transparent Data Encryption
- Encrypting a SQL Server Database Backup
- …check all
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
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).