January 22, 2008 at 1:33 pm
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
January 22, 2008 at 1:56 pm
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?
January 22, 2008 at 8:00 pm
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
Change is inevitable... Change for the better is not.
January 23, 2008 at 7:39 am
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
January 23, 2008 at 8:50 am
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?
January 23, 2008 at 10:15 am
Do we have work around on this to secure our database without dropping the xp_cmdshell stored procedure from database?
Thanks
Ambrosius Bekti
January 23, 2008 at 10:39 am
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?
January 23, 2008 at 11:27 am
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"
January 23, 2008 at 6:54 pm
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
Change is inevitable... Change for the better is not.
January 24, 2008 at 11:45 am
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
January 24, 2008 at 12:07 pm
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?
January 25, 2008 at 4:44 am
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
---------------------------------------------------------------------
January 25, 2008 at 8:04 am
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.
January 25, 2008 at 9:30 am
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
January 25, 2008 at 10:37 pm
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