Are there best practices for setting permissions for system stored procedures

Question

I am looking at addressing some potential security concerns identified by server scans, specifically, the following stored procedures are granted to the public role: sp_createorphan, sp_replcmds, sp_replsetsynstatus (which may be depricated but still shows in the scan results), sp_replwritetovarbin, sp_resyncexecute and sp_unprepare.

Is there a best practice for the permissions on these?  Issues from removing the public role?

I am seeing these on a 2008 SQL express being used as a witness server and both SQL 2005 and SQL 2008 Enterprise.  The witness is part of a SharePoint farm and the others are hosting application databases; some in house applications and some 3rd party COTS applications.  

I expect removing public from some of these will break some of the 3rd party applications using ODBC, but would like to hear from others before breaking something I have to fix. :)

Thanks.

Answer 1

Hi qman,

You want remove sp_createorphan, sp_replcmds, sp_replsetsynstatus, sp_replwritetovarbin, sp_resyncexecute and sp_unprepare from the public role. The sp_createorphan stored procedures is used for ODBC ntext, text, and image processing. It you remove it from public role, I think it would have an impact in ODBC.

Hope this helps,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

Pretty much what I'm expecting and why I posted question.  Thanks for the follow up Maggie, but you're pretty much restating what I summarized in the last paragraph of my post.  

I'm probably going to have to deny execute from public on these, but if it comes to that, I'll try granting execute to the DBO role; under the assumption that any 3rd party application is likely to be connecting as something other than public and the DBO role having execute won't cause issues in a server scan.  I was hoping to hear of others that might have address this and how they might have worked through issues?

I'm more concerned with the extended stored procedures supporting replication, as the SQL Express I'm using as a witness server for my SharePoint 2007 mirrors has these issues.

At the very least this has raised a few eyebrows on the number of SQL Express tracks left on workstations around the workplace, long after the 3rd party applications are no longer used. 

Thanks.
Guy