Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Extended Stored procedures - SQL 2000 Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:09 PM
Points: 6, Visits: 437
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_SCP Add, change, or delete Active Directory directory service objects
sp_adddistpublisher Replication
sp_adddistributiondb Replication
sp_attachsubscription Replication
sp_changedistpublisher Replication
sp_copysubscription Replication
sp_MScopysnapshot Replication
sp_MScopyscriptfile Replication install
sp_MSget_file_existence Replication install
sp_MSremove_userscript Replication install
sp_replicationoption Replication
sp_vupgrade_replication Replication install
sp_MSreplremoveuncdir Replication called from distribution database
sp_MSdeletefoldercontents Replication called from distribution database
sp_resolve_logins Log shipping
Sp_set_local_time MSDB
sp_msx_defect MultiServer administration
sp_msx_enlist MultiServer administration
• Xp_sscanf
• Xp_sprintf
• Xp_msver
• Xp_msver
• Xp_enumgroups
• Xp_logevent
• Xp_loginconfig

Thanks in advance
Bekti
Post #446101
Posted Tuesday, January 22, 2008 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:57 PM
Points: 7,064, Visits: 15,270
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?
Post #446114
Posted Tuesday, January 22, 2008 8:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Heh... make sure you get rid of that pesky TempDB database while your at it. :P 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... ;) :P :D :)

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #446195
Posted Wednesday, January 23, 2008 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:09 PM
Points: 6, Visits: 437

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
Post #446440
Posted Wednesday, January 23, 2008 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:57 PM
Points: 7,064, Visits: 15,270
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?
Post #446487
Posted Wednesday, January 23, 2008 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:09 PM
Points: 6, Visits: 437

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

Thanks
Ambrosius Bekti
Post #446531
Posted Wednesday, January 23, 2008 10:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:57 PM
Points: 7,064, Visits: 15,270
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?
Post #446541
Posted Wednesday, January 23, 2008 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2008 12:35 PM
Points: 1, Visits: 12
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"
Post #446563
Posted Wednesday, January 23, 2008 6:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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"? :P ) 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #446716
Posted Thursday, January 24, 2008 11:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 30, 2014 9:13 PM
Points: 959, Visits: 2,885
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
Post #447122
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse