-- 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 seeing metadata for databases they can't access.
-- Unfortunately users can still enumerate all database names via db_name
-- This makes database enumeration slightly slower, but that's irrelevant in
-- scenarios.
USE master
REVOKE VIEW ANY DATABASE FROM public
 
 
-- Don't give msdb access by default. Grant access only to users who need it.
USE msdb
REVOKE CONNECT TO guest
 
 
-- View server permissions
SELECT
	State_Desc, Permission_Name, class_desc,
	COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
	Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
ORDER BY SecurableName
 
-- View membership in Server roles
SELECT Roles.Name, Roles.Type_Desc, Members.Name MemberName, Members.Type_Desc
FROM sys.server_role_members RoleMembers
INNER JOIN sys.server_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.server_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id
 
 
-- Keep the vagabonds out, make the  database invite only.
IF (db_name() NOT IN ('master', 'tempdb')) REVOKE CONNECT TO guest
 
-- Move all of the permissions given to public 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