sp_check: SQL Server Database Checks

Explicit permissions granted to the public role

What's the issue?

One or more explicit permissions have been granted to the public role on your SQL Server instance.

Why is this a problem?

By default, the public role includes anyone who has a login that allows them to connect to your instance, and it include very few permissions. There is very little reason for the public role to be granted any explicit permissions, since there is virtually no permission that everyone who has a login needs on your SQL Server.

Moreover, there are some permissions like BACKUP DATABASE that can allow anyone to make a copy of your database. Allowing anyone to make database backups is a great way to encourage a data breach.

Straight Path classifies this vulnerability as level 2, "High - review required" in sp_CheckSecurity.

What should you do about this?

Review any permissions assigned to the public role and reassign them only to necessary users or groups. After permissions are assigned as needed, revoke the explicit permissions found for the public role.

What do the Vulnerability Levels mean?

0 - Information only. This is stuff you should know about your instances like version and service account used, but if you don't know it…well, now you do.

1 - High vulnerability requiring action. These are the issues that could most likely lead to your company being front page news for all the wrong reasons. If your instances have any results at this level then we recommend cancelling that 3-martini lunch and instead huddling with your team to figure out when to address these issues.

2 - High vulnerability to review. These include settings and assigned permissions you should review soon, if not immediately. These findings may not necessarily indicate a clear vulnerability, but we've found unexpected vulnerabilities in these categories at many, many clients.

3 - Potential vulnerability to review. These are configurations or assigned permissions you may be using that could lead to problems for users. Or maybe they're just required for your applications. Either way, we recommend reviewing these to make sure these are correct.

4 – Low vulnerability with recommended action. These are typically security inconsistencies that should be addressed. They aren't likely to cause problems, but you should clean up the mess.