SQL 2005 Security - Revoke EXECUTE rights for PUBLIC on (potentially) unsafe extended stored procedures

Where I work, we have an amazing crew of security architects and analysts who have decades of experience in all things security. Sure, at times they may seem paranoid, but that's just because they've seen bad things that you and I couldn't even dream up. Recently I've been going through our security baseline to verify we're being as secure as possible with SQL (on the servers our team supports) and I'd like to share some code that will help identify any extended stored procedures (from SQL 2005) that our security folks have deemed potentially unsafe (when PUBLIC has been granted EXECUTE rights), as well as code to REVOKE those rights.

THE LIST


Here's the list of extended stored procedures that some folks have deemed potentially unsafe if PUBLIC could execute them:

xp_availablemedia
xp_cmdshell
xp_deletemail
xp_dirtree
xp_dropwebtask
xp_enumerrorlogs
xp_enumgroups
xp_findnextmsg
xp_fixeddrives
xp_getnetname
xp_logevent
xp_loginconfig
xp_makewebtask
xp_regread
xp_readerrorlog
xp_readmail
xp_runwebtask
xp_sendmail
xp_servicecontrol
xp_sprintf
xp_sscanf
xp_startmail
xp_stopmail
xp_grantlogin
xp_revokelogin
xp_logininfo
xp_subdirs
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumkeys
xp_regenumvalues
xp_regremovemultistring
xp_regwrite


Well, I don't know about you, but I don't want to go one-by-one through these 30-some objects, on each of our 30-some SQL servers and check/revoke rights. So I wrote my own little SQL script to identify any of these XPs (eXtended stored Procedures)

SQL TO VIEW EXTENDED STORED PROCS FOR WHICH PUBLIC HAS RIGHTS

This script will identify any of the said XPs on a SQL 2005 server which have EXECUTE rights granted to PUBLIC

SNIPPET #1 - Identify extended stored procedures for which PUBLIC has rights


USE MASTER;

SELECT
OBJECT_NAME(major_id) AS [Extended Stored Procedure],
USER_NAME(grantee_principal_id) AS [User]
FROM
sys.database_permissions
WHERE
OBJECT_NAME(major_ID) IN ('xp_availablemedia','xp_cmdshell',
'xp_deletemail','xp_dirtree',
'xp_dropwebtask','xp_enumerrorlogs',
'xp_enumgroups','xp_findnextmsg',
'xp_fixeddrives','xp_getnetname',
'xp_logevent','xp_loginconfig',
'xp_makewebtask','xp_regread',
'xp_readerrorlog','xp_readmail',
'xp_runwebtask','xp_sendmail',
'xp_servicecontrol','xp_sprintf',
'xp_sscanf','xp_startmail',
'xp_stopmail','xp_grantlogin',
'xp_revokelogin','xp_logininfo',
'xp_subdirs','xp_regaddmultistring',
'xp_regdeletekey','xp_regdeletevalue',
'xp_regenumkeys','xp_regenumvalues',
'xp_regremovemultistring','xp_regwrite')
AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
ORDER BY 1;

OUTPUT #1

xp_regread public
xp_cmdshell public


Now, if you want to revoke the rights, you can modify that code so that it outputs a bunch of REVOKE statements which you can copy and then run from SQL Management Studio

SNIPPET #2 - Create REVOKE statements


USE MASTER;

SELECT
'REVOKE ALL ON ' + OBJECT_NAME(major_id) + ' FROM ' + USER_NAME(grantee_principal_id)
FROM
sys.database_permissions
OBJECT_NAME(major_ID) IN ('xp_availablemedia','xp_cmdshell',
'xp_deletemail','xp_dirtree',
'xp_dropwebtask','xp_enumerrorlogs',
'xp_enumgroups','xp_findnextmsg',
'xp_fixeddrives','xp_getnetname',
'xp_logevent','xp_loginconfig',
'xp_makewebtask','xp_regread',
'xp_readerrorlog','xp_readmail',
'xp_runwebtask','xp_sendmail',
'xp_servicecontrol','xp_sprintf',
'xp_sscanf','xp_startmail',
'xp_stopmail','xp_grantlogin',
'xp_revokelogin','xp_logininfo',
'xp_subdirs','xp_regaddmultistring',
'xp_regdeletekey','xp_regdeletevalue',
'xp_regenumkeys','xp_regenumvalues',
'xp_regremovemultistring','xp_regwrite')
AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
ORDER BY 1;

OUTPUT #2


REVOKE ALL ON xp_regread FROM PUBLIC
REVOKE ALL ON xp_cmdshell FROM PUBLIC

IMPORTANT: This didn't revoke anything, this only created some REVOKE statements that you can copy from the result set into your own query window and execute them. THEN the revoking will happen.