Extended Stored procedures - SQL 2000

  • Hi All,

    Are any of the following stored procedures used for legitimate production purposes on any of our SQL 2000 servers? I am already in the process of dropping the xp_cmdshell stored procedure on all SQL 2000 servers.

    The following table lists the components that will break if you remove the xp_cmdshell stored procedure.

    Stored procedure Purpose

    ============= ===============

    sp_ActiveDirectory_SCPAdd, change, or delete Active Directory directory service objects

    sp_adddistpublisherReplication

    sp_adddistributiondbReplication

    sp_attachsubscriptionReplication

    sp_changedistpublisherReplication

    sp_copysubscriptionReplication

    sp_MScopysnapshotReplication

    sp_MScopyscriptfileReplication install

    sp_MSget_file_existenceReplication install

    sp_MSremove_userscriptReplication install

    sp_replicationoptionReplication

    sp_vupgrade_replicationReplication install

    sp_MSreplremoveuncdirReplication called from distribution database

    sp_MSdeletefoldercontentsReplication called from distribution database

    sp_resolve_loginsLog shipping

    Sp_set_local_timeMSDB

    sp_msx_defectMultiServer administration

    sp_msx_enlistMultiServer administration

    •Xp_sscanf

    •Xp_sprintf

    •Xp_msver

    •Xp_msver

    •Xp_enumgroups

    •Xp_logevent

    •Xp_loginconfig

    Thanks in advance

    Bekti

  • Why not just completely lock down access to xp_cmdshell? You're going to destroy your ability to ever do replication, resolve login if you attach a foreign DB, and scuttle your ability to log any custom errors to the event log. Never mind all of the various user-defined things you might need to do.

    Why such a drastic approach? What are you trying to prevent?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Heh... make sure you get rid of that pesky TempDB database while your at it. 😛 All it does is take up room and cause headaches... saves a lot more space than deleting system stored procedures and it absolutely guarantees that you will never, ever have security problems with your database... 😉 😛 😀 🙂 :hehe: :w00t:

    I agree with Matt... you don't know what the long term effects of deleting those system objects will be. For example, do you actually know if any of those sprocs are used in an upgrade path or not? Do you know if any of those procs are used by other critical procs?

    There's ways to secure your database without pulling the wings off of it...

    Don't do it!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Guys for your response. I have discussed this with my Big Manager about the effect the database if we remove the xp_cmdshell stored procedure. He said that the stored procedure will be removed from the system. So I can’t do anything now.:(

    Again thanks for a great help.

    Bekti

  • You might care to save your big manager from himself, and leave yourself a back door.

    Just be sure to SAVE A COPY of these. In particular - keep a copy of the syscomments records related to this. Trust me - you will need these in the future, possibly as early as the next Service pack you need to apply.

    Also - make a permanent backup of your system DB's before you do this. You might as well read up on how to restore those, and be ready to do so at a moment's notice.

    Nothing like have to reverse engineer SSP's back into a database (which will be deemed corrupted by MS who will then instruct you to restore the database, or reinstall SQL Server).

    In case you didn't catch that from my previous post - I've been in this scenario (where a series of SSP's went "missing"), and let's just say - it was a BIG deal, and took a long time and multiple issues opened and heated discussions had with MS support.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Do we have work around on this to secure our database without dropping the xp_cmdshell stored procedure from database?

    Thanks

    Ambrosius Bekti

  • As long as you're not allowing SA access to folks, then you should be able to use DENY EXECUTE

    against all users. even if the users had DBOwner access to the database, DENY would take precedence.

    Since SA bypasses the security checks, you'd still be able to use those accounts in case you need to use the XP.

    If you are allowing SA access, then you have bigger issues to deal with. Your best bet might be to "obfuscate" it, by renaming it. I'm not a fan, since that also has potential for causing the same problems we were trying to avoid. Your plan would then be to REMOVE SA access from all users (including and possibly especially from developers), then go back to review item #1.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • If my "big manager" ever asked me to do something like this I'd be sure to have a long paper/email trail that will direct the even bigger managers back to him when everything falls apart.

    I have a motto/mantra/rule of thumb/ that I teach all our staff,

    "If you are not absolutely certain of the outcome of your actions, do not proceed"

  • Not only should you get the BIG Manager to put it in writing, but you should make a full backup of the 4 system databases, which includes the Master database, before any such deletions are done. That way, you can save the BIG Manager's (Big "Mangler"? 😛 ) life right after you say, "I told you so" if the general predictions of doom should actually occur. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There's trouble right here in River City.

    That's trouble that starts with 'T'

    That rhymes with 'B'

    That stands for Big Manager.

    I'd certainly get everything in writing and I might even start sending my resume around. Something smell odd about this.

    Todd Fifield

  • By the way - I just noticed you posted this to the 2005 board. If you ARE in 2005, there's a really simple way to disable it... disable it in the Surface area configuration tool.

    And *poof* - no xp_cmdshell running.

    Of course - if you have rogue admin issues, any of them can turn it back on at any time.......

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • sounds like the security police have been in with their tick list.............

    how about revoke any access to these from public, if something needs one to work, create a role on the db, add the user and grant the permission they need

    ---------------------------------------------------------------------

  • There's a lockdown script, for SQL Server 2000, over at SQLSecurity that has code to reduce the exposer to xp_cmdshell without deleting it or any other system stored procedures.

  • Hi Mike,

    Could you please send me the script to lockdown xp_cmdshell for SQL Server 2000 if you don’t mind?

    Thank you

    Ambrosius Bekti

  • Sent you a pm to the site containing the script.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply