Click here to monitor SSC
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ambrosius.bekti
ambrosius.bekti
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

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

Bekti
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
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
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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"
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tfifield
tfifield
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 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