But what this role does? 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:
I have recently released a brand new software tool called “DBA Security Advisor” which checks SQL Server instances for vulnerabilities using a large number of security checks. Among other, it checks if the Public database role has been granted additional permissions besides the defaults. Check it out here!
Recommended eBooks on SQL Server: