SQL Server with Mr. Denny

Feb 3 2009   2:47AM GMT

Rights to System Stored Procedures

Denny Cherry Denny Cherry Profile: Denny Cherry


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.


 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: