SQL SERVER – Security Risk of Public Role – Very Little

I like to do consulting for SQL Server Performance Tuning area because that is my favorite thing to do and I spend over 16 hours a day tuning my system. However, once in a while, during the Comprehensive Database Performance Health Check, I do receive questions about pretty much anything and recently I received a question about Public Role.

Let us read the question as exactly it was asked –

“We see a database role as public in our SQL Server. Should we remove the public database role from every single database? What is the security risk of keeping it?”

A very simple question and I have an equally clear answer.

Limitation

I personally, do not have any problem with the public database role as every single database user is part of it. If you create a database user, by default it has all the rights which you give it to a public role. Once you start adding it to another role, it will start inheriting various different roles.

While the user has an only public role, it has extremely limited permission and honestly, it can’t do much. The role can’t change anything related to database configuration or data in any database. This is why I am personally not bothered by this particular role.

Advantage

However, at any point of time, if your admin wants to give certain rights to every single user in every single database, they can directly give that particular right to the public role and immediately all the users will have that particular right (permissions). This particular feature can turn out to be very handy for DBA.

My View – Public Role

Once, I have done a huge exercise at one of my existing clients to remove every user from the public role and it was one of the biggest nightmares for us. It took us many hours to complete the task and after completing the task, we realized it was not worth the effort of what we achieved.

Remember that public role is unlike another fixed role where we can’t change any permissions. In this case, we can grant, remove or alter any permissions to this role and one should use this as your advantage.

Reference: Pinal Dave (https://blog.sqlauthority.com)