/*
Copyright (c) 2007 Gustavo G. Duarte (http://duartes.org/gustavo)

This file is licensed under the terms of the MIT/X11 open source license. 
See copying.txt in the root directory of this project (above /src) or 
http://en.wikipedia.org/wiki/MIT_License for the terms of copyright.
*/

-- Please don't run this script in a production server until you've tested its effects.
-- Run it inside a transaction so that you can ROLLBACK in case of trouble.
-- Also, MAKE SURE you are not using options turned off by this script, such as
-- SQL Mail and replication.

-- Don't give msdb access by default. Grant access only to users who need it.
USE msdb
REVOKE CONNECT TO guest

-- Revoke EXECUTE permissions from public on all extended stored procedures. These procedures
-- aid attackers in compromising your server and network.
USE master

DECLARE DangerousStoredProcedures CURSOR STATIC READ_ONLY FOR
SELECT name
FROM sys.all_objects
WHERE type = 'X' OR (type = 'P' AND name like 'xp%')

DECLARE
	@SpName			sysname,
	@FullCommand	nvarchar(4000)

OPEN DangerousStoredProcedures
FETCH NEXT FROM DangerousStoredProcedures INTO @SpName

WHILE (@@FETCH_STATUS = 0) BEGIN
	SET @FullCommand = 'REVOKE EXECUTE ON [' + @SpName + '] FROM public'
	EXEC sp_executesql @FullCommand

	FETCH NEXT FROM DangerousStoredProcedures INTO @SpName
END

DEALLOCATE DangerousStoredProcedures

-- Prevent users from enumerating all databases. This makes database enumeration
-- slightly slower, but in most instances it's a good tradeoff.
USE master
REVOKE VIEW ANY DATABASE FROM public

-- We now change several configuration options for SQL Server. SQL Server Books explains
-- each of the sp_configure options we use below, but some blurbs are provided
EXECUTE sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

-- This is OFTEN ABUSED and is a major method of privilege escalation and other nasties.
-- Unless you MUST use OPENROWSET and OPENDATASOURCE, keep this off.
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0

-- If you're not running SQL Agent, leave them off
EXECUTE sp_configure 'Agent XPs', 0

-- Unless you're running .NET assemblies within SQL Server, you can safely
-- keep this off
EXECUTE sp_configure 'clr enabled', 0

-- This is almost never necessary and carries high risk.
EXECUTE sp_configure 'cross db ownership chaining', 0

-- Unless you send emails from SQL Server, leave this off.
EXECUTE sp_configure 'Database Mail XPs', 0

-- Prevent malicious or accidental Denial of Service (DoS) attacks against your server
-- Tune according to your server's memory and situation
-- BEWARE: If you set this to a value that's too low, you can SLAUGHTER your server's
-- performance
--
--EXECUTE sp_configure 'max server memory (MB)', MAX_MEMORY_IN_MEGABYTES

-- Disable OLE (COM) instatiation from SQL. This is an abomination, keep it off
-- unless you really need it.
EXECUTE sp_configure 'Ole Automation Procedures', 0

-- Configure an upper limit, in SECONDS, for the estimated running time of a query
-- If a query is estimated to run longer than this, it WILL NOT RUN AT ALL, but rather
-- will be aborted. This offers some protection against runaway queries, but it only
-- works in an individual query level, not for a whole batch. 
-- BEWARE: For most people it's
-- better to keep it off, as the trade off in security/potential for weird 
-- weekend-ruining bug is bad.
-- 
-- EXECUTE sp_configure 'query governor cost limit', MAX_RUNNING_TIME_IN_SECONDS

-- As my grandpa used to say, "Always disable ancient features that can bite you freshly in the ass."
-- This was superseded by linked servers.
EXECUTE sp_configure  'remote access', 0

-- obscure, leave it off unless you know you need them
EXEC sp_configure 'remote admin connections', 0

-- If you don't use replication, turn these off.
EXEC sp_configure 'Replication XPs', 0

-- If you don't use SMO and SQL-DMO, turn them off.
-- BEWARE: this is commonly used, if you don't know what they are leave them on
-- EXEC sp_configure 'SMO and DMO XPs', 0

-- Unless you need to send email from the database, leave it off
EXEC sp_configure 'SQL Mail XPs', 0

-- What the hell is 'web assistant'? Sounds like something my mom could use.
-- This is deprecated anyway, leave it off.
EXEC sp_configure 'Web Assistant Procedures', 0

-- Ahhh, this is the biggie. As you read this, a teenager somewhere is 
-- owning a server via xp_cmdshell. Unless you MUST run this, leave it off.
EXEC sp_configure 'xp_cmdshell', 0
GO

RECONFIGURE WITH OVERRIDE
GO