Rights to System Stored Procedures

Published On: 2009-02-03By:

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.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link