SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extended Stored procedures - SQL 2000


Extended Stored procedures - SQL 2000

Author
Message
ambrosius.bekti
ambrosius.bekti
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29051 Visits: 19002
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211987 Visits: 41977
Heh... make sure you get rid of that pesky TempDB database while your at it. Tongue 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... Wink Tongue BigGrin Smile 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ambrosius.bekti
ambrosius.bekti
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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.Sad
Again thanks for a great help.

Bekti
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29051 Visits: 19002
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?
ambrosius.bekti
ambrosius.bekti
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29051 Visits: 19002
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?
Kevin D
Kevin D
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211987 Visits: 41977
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"? Tongue ) life right after you say, "I told you so" if the general predictions of doom should actually occur. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tfifield
tfifield
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 2890
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search