/*
Copyright (c) 2008 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 database until you've tested its effects.
-- Run it inside a transaction so that you can ROLLBACK in case of trouble.

-- You must choose the database to harden in the line below
USE DATABASE_YOU_WITH_TO_HARDEN_AND_HAVE_BACKED_UP_PRIOR_TO_RUNNING_SCRIPT
GO

-- Revoke all of the permissions from the builtin public role and grant them to FormerPublic
CREATE ROLE FormerPublic

DECLARE PublicPermissions CURSOR STATIC READ_ONLY FOR
SELECT 
	Permission_Name,
	COALESCE(O.name, DB_NAME(Perms.major_id)) SecurableName,
	SCHEMA_NAME(O.schema_id) [Schema]
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
WHERE Grantees.Name = 'public' AND Grantees.Type_Desc = 'DATABASE_ROLE'

DECLARE
	@PermissionName sysname,
	@SecurableName  sysname,
	@SchemaName     sysname,
	@Arguments      nvarchar(4000),
	@FullCommand    nvarchar(4000)

OPEN PublicPermissions

FETCH NEXT FROM PublicPermissions
INTO @PermissionName, @SecurableName, @SchemaName

WHILE (@@FETCH_STATUS = 0) BEGIN
	IF (@SecurableName IS NOT NULL) BEGIN
		SET @Arguments = @PermissionName + ' ON ' 
			+ COALESCE('[' + @SchemaName + '].', '') + '[' + @SecurableName + ']'

		SET @FullCommand = 'REVOKE ' + @Arguments + ' FROM public'
		EXEC sp_executesql @FullCommand

		SET @FullCommand = 'GRANT ' + @Arguments + ' TO FormerPublic'
		EXEC sp_executesql @FullCommand
	END

	FETCH NEXT FROM PublicPermissions
	INTO @PermissionName, @SecurableName, @SchemaName
END

DEALLOCATE PublicPermissions

-- Keep the vagabonds out, make the  database invite only.
IF (db_name() NOT IN ('master', 'tempdb')) REVOKE CONNECT TO guest