Posted by: Denny Cherry
Permissions, System Objects
Why on earth to people want to go changing the rights on the system objects.
99% of the time the rights are exactly what they need to be. Now sometimes you may need to tweak them so that no one has rights to create jobs on the production SQL Servers but they do have the ability to query the tables, so you revoke the rights to sp_add_job. I can see that (heck I’ve even done it before).
But someone’s auditor told them that they needed to remove the rights to the extended stored procedure sp_reset_connection. Now this isn’t something that is normally called by a client app. It’s called automatically by the SQL driver when the client app is going to reconnect to a pooled connection to let the SQL Server know to clean up it’s data so that the connection is ready to be reused.
Did the auditor know what this procedure was used for? Probably not. Most people don’t have the slightest idea what this procedure is, or what it does. If you watch SQL Profiler you’ll see it go scrolling by with no idea what it is used for. Why did this auditor decide that in order to pass the audit this procedure shouldn’t be allowed to be used? I’ve got no idea, but I would imagine that at some point some DBA at some other companies told them that they didn’t use it, and that they disabled it. As long as no connection pooling was being used they probably didn’t notice any problems, however if temp tables are left in the session, there could be a collision.
If this was my system, I’d go to my manager and explain what this was, and what it was used for. I’d then go to the auditor and let them know that the rights weren’t going to be changed.
Before just blindly following an order to change rights on any system object be sure to see what will happen when that procedure isn’t available to other users. Removing rights to system procedures could end up having disastrous results if you are not careful.