Remove Exec Permissions To Extended Stored Procs

-- =================================================
-- Remove Exec Permissions To Extended Stored Procs 
-- =================================================
-- Author: Stevo Smocilac
-- Created: 7 November 2006
-- Version: 1.0

-- Description --
-- This script will find any user or role which has execute permissions on any of the stored procedures in the “#High_Risk_Stored_Procs” temp table and generate the relevant revoke statements to remove these permissions.

SET NOCOUNT ON
Use tempdb
GO
--Create all temp tables
CREATE TABLE #Access_To_Extended_Stored_Procs
(ProcName varchar(255),
Username varchar(255))
GO
CREATE TABLE #High_Risk_Stored_Procs
(Stored_Proc varchar(255) NULL)
GO

--Insert data into the temp tables
--Get a list of all the users which have access to any of the extended stored procedures in the master db
INSERT INTO #Access_To_Extended_Stored_Procs
SELECT master.dbo.sysobjects.name AS ProcName, master.dbo.sysusers.name AS Username
FROM master.dbo.sysobjects 
INNER JOIN master.dbo.sysprotects 
ON master.dbo.sysobjects.id = master.dbo.sysprotects.id 
INNER JOIN master.dbo.sysusers 
ON master.dbo.sysprotects.uid = master.dbo.sysusers.uid 
WHERE     (master.dbo.sysobjects.xtype IN ('X', 'P')) 
AND (master.dbo.sysprotects.protecttype IN ('204', '205'))
ORDER BY master.dbo.sysobjects.name
GO

--Populate the table of high risk stored procedures, you can add new procedures here if required
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OACreate')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OADestroy')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OAGetErrorInfo')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OAGetProperty')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OAMethod ')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OASetProperty ')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_OAStop')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('sp_SdiDebug')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_availablemedia')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_cmdshell')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_deletemail')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_displayparamstmt ')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_dropwebtask')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_dsninfo')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_enumdsn')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_enumerrorlogs')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_enumgroups')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('XP_EnumQueuedTasks')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_eventlog')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_execresultset ')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_fileexist')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_findnextmsg')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_fixeddrives')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_getfiledetails')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_getnetname')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_grantlogin')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_logevent')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_loginconfig')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_logininfo')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_makewebtask')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_msver')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_ntsec_enumdomains')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('XP_PerfSample')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('XP_PerfStart')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_printstatements ')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_readerrorlog')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_readmail')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regaddmultistring')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regdeletekey')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regdeletevalue')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regenumkeys')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regenumvalues')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regread')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regremovemultistring')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_regwrite')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_revokelogin')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_runwebtask')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_sendmail')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_servicecontrol')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_Snmp_GetState')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_Snmp_RaiseTrap')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_sprintf')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_SqlInventory')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_SqlRegister')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_SqlTrace')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_sscanf')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_startmail')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_stopmail')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_subdirs')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_Terminate_Process')
INSERT [#High_Risk_Stored_Procs] ([Stored_Proc]) VALUES ('xp_unc_to_drive')
GO

--Join the data from the two temp tables and create the revoke script
use [master]
GO
SELECT 'use [master]
'+'REVOKE EXECUTE ON [dbo].['+#High_Risk_Stored_Procs.Stored_Proc+'] TO ['+#Access_To_Extended_Stored_Procs.Username+']' +'
GO'
FROM#Access_To_Extended_Stored_Procs 
INNER JOIN #High_Risk_Stored_Procs ON #Access_To_Extended_Stored_Procs.ProcName = #High_Risk_Stored_Procs.Stored_Proc 

--Cleanup temp tables
Drop table #Access_To_Extended_Stored_Procs
Drop table #High_Risk_Stored_Procs
GO