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 SQL Server Administration tips via live demonstrations and hands-on guides!
Check my online course on Udemy 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!
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)