SQL Server with Mr. Denny

Apr 29 2015   4:00PM GMT

Stop Touching the Public Database Role

Denny Cherry Denny Cherry Profile: Denny Cherry

SQL Server
SQL Server security

Odds are you don’t need to make permissions changes to the public database role. Back in the SQL 7 days making changes to it was pretty common as the database engine wasn’t all that well secured. Today in the SQL 2005 and newer times there’s no reason to change any of the default permissions that Microsoft has setup.

I’ve seen auditor requirements to remove access to things like xp_instance_regread as this could potentially access things in the registry that an attacker could use. I suppose this is true, if you haven’t setup the startup accounts correctly. If SQL is running under an account which is a member of the local Administrators group then yes someone could read registry keys which they shouldn’t have access to. But there also shouldn’t be anything all that sensitive written to the registry on a production SQL Server. SQL puts basically nothing in there other than a few startup parameters and there shouldn’t be really anything else installed on the system. Anything sensitive that Windows writes to the registry is going to be encrypted.

Now what’ll break by changing these permissions could be just about anything. I know that if users don’t have access to run xp_instance_regread for example they won’t be able to use SSMS as they’ll get an error when they connect to the instance. So to make the error go away you now have to grant the EXECUTE right to xp_instance_regread for every user that connects to the database engine with SSMS (which is probably most of them). This means that removing it from public was meaningless as it’s still granted to everyone that connects, just via their login.

Now I know that it’s a lot easier for me to write about this than it is for you to defend leaving it to an auditor or to management, but hopefully you can get logic to prevail. So unless you really want to do a lot of security related troubleshooting that you shouldn’t need to do, don’t be messing with the public security permissions.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: