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.
Denny
