locked
Disabling Extended Stored Procedures RRS feed

  • Question

  • Hey guys, I'm trying to disable some extended stored procedures on my sql server 05 but encountered this error.

    The query I made: DENY EXECUTE on <procedurename> to public

    The response I received: Permission on erver scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is member

    So do I have to assign some sort of permissions to myself or to the database itself or it requires a different solution altogether.

    Thanks

    Insahn.

     

    Monday, October 26, 2009 4:05 AM

Answers

  • I have no idea who wrote whatever you are reading, but it isn't correct.  Every time I see something like this, whatever you are referencing was usually written by someone who read something written a decade or more ago for SQL Server 6.5 or 7.0 and has no clue what a SQL Server is or how to manage security for one.  It is 100% inapplicable to SQL Server 2005 and above.

    Every system stored procedure and extended stored procedure is secured.  They only expose information that a user already has access to and do not allow authority to be escalated.  Your SQL Server is already hardened, right out of the box.  You make it less secure by enabling features that are disabled by default or by granting access to things that are not granted by default.  Microsoft will not support a SQL Server instance where you have removed or disabled any system object that ships with the product.  So, while you can do something like this, it is VERY STRONGLY discouraged and 100% unsupported.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 26, 2009 12:35 PM

All replies

  • Why are you trying to disable extended stored procedures that ship with SQL Server?  This is not recommended and is not supported.  If you remove or disable anything that ships with SQL Server and you have an issue that requires you to open a support case, PSS will require you to put your instance back into a supported state before dealing with any issue.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 26, 2009 4:12 AM
  • Why are you trying to disable extended stored procedures that ship with SQL Server?  This is not recommended and is not supported.  If you remove or disable anything that ships with SQL Server and you have an issue that requires you to open a support case, PSS will require you to put your instance back into a supported state before dealing with any issue.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals

    Well I am assigned to perform SQL hardening and the baseline security that I have to comply to, requires me to disable a number of stored procedures. This is also recommended both by CIS and DISA. Honestly this is my first time hardening SQL, so any input is appreciated.
    Monday, October 26, 2009 6:57 AM
  • I have no idea who wrote whatever you are reading, but it isn't correct.  Every time I see something like this, whatever you are referencing was usually written by someone who read something written a decade or more ago for SQL Server 6.5 or 7.0 and has no clue what a SQL Server is or how to manage security for one.  It is 100% inapplicable to SQL Server 2005 and above.

    Every system stored procedure and extended stored procedure is secured.  They only expose information that a user already has access to and do not allow authority to be escalated.  Your SQL Server is already hardened, right out of the box.  You make it less secure by enabling features that are disabled by default or by granting access to things that are not granted by default.  Microsoft will not support a SQL Server instance where you have removed or disabled any system object that ships with the product.  So, while you can do something like this, it is VERY STRONGLY discouraged and 100% unsupported.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Monday, October 26, 2009 12:35 PM
  • I'm also assigned to perform SQL hardening and the baseline/benchmark security for SQL Server 2005 for the auditors, I'm using the documentation provided by CIS. So, if the CIS benchmark documentation is incorrect. Does Microsoft provided their own Benchmarking documentation that I could use?
    Monday, February 1, 2010 4:23 PM
  • I have no idea who or what CIS is, but if there is anything in the document that tells you to remove any object that is shipped with SQL Server 2005, it is invalid.  Doing so will leave you with an unsupported installation.  Microsoft will not provide any support until you put back all of the things that ship as part of the product.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, February 2, 2010 6:14 AM
  • My apologizes, the document we're using is from "The Center for Internet Security" from http://www.cisecurity.org/. They provided benchmarks for Microsoft SQL Server 2005. However, thank you for your input on this matter. I'll be sure to skip the disabling of the extended stored procedures.
    Tuesday, February 2, 2010 4:05 PM