permissions-manager-install.sql

/**************************************************************************
	PERMISSIONS MANAGER
	Author: Eric Cobb - http://www.sqlnuggets.com/
	Supported Versions: SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
	License:
			MIT License
			
			Copyright (c) 2017 Eric Cobb

			Permission is hereby granted, free of charge, to any person obtaining a copy
			of this software and associated documentation files (the "Software"), to deal
			in the Software without restriction, including without limitation the rights
			to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
			copies of the Software, and to permit persons to whom the Software is
			furnished to do so, subject to the following conditions:

			The above copyright notice and this permission notice shall be included in all
			copies or substantial portions of the Software.

			THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
			IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
			FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
			AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
			LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
			OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
			SOFTWARE.
***************************************************************************/

--Change this to whatever database you want to create the Permissions Manager objects in.
USE [Master]
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name] = N'perms' ) 
    EXEC sp_executesql N'CREATE SCHEMA [perms] AUTHORIZATION [dbo];';
GO

/**************************************************************************
	Create Tables
***************************************************************************/


IF OBJECT_ID('perms.Snapshots') IS NULL
BEGIN
	CREATE TABLE [perms].[Snapshots](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Snapshots] PRIMARY KEY CLUSTERED ,
		[DatabaseName] [nvarchar](128) NOT NULL,
		[CaptureDate] [datetime] NOT NULL CONSTRAINT [DF_Perms_Snapshots_CaptureDate]  DEFAULT (getdate())
	);
END
GO


IF OBJECT_ID('perms.DatabasePermissions') IS NULL
BEGIN
	CREATE TABLE [perms].[DatabasePermissions](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_DatabasePermissions] PRIMARY KEY CLUSTERED,
		[SnapshotID] [bigint] NOT NULL,
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);

	ALTER TABLE [perms].[DatabasePermissions]  WITH CHECK ADD CONSTRAINT [FK_Perms_DatabasePermissions_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.ObjectPermissions') IS NULL
BEGIN
	CREATE TABLE [perms].[ObjectPermissions](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Object_Permissions] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[SchemaName] [nvarchar](128) NOT NULL,
		[ObjectName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL,
		[ClassDesc] [nvarchar](60) NOT NULL,
		[ColumnName] [nvarchar](128) NULL
	);

	ALTER TABLE [perms].[ObjectPermissions]  WITH CHECK ADD CONSTRAINT [FK_Perms_ObjectPermissions_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.RoleMemberships') IS NULL
BEGIN
	CREATE TABLE [perms].[RoleMemberships](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Role_Memberships] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[RoleName] [nvarchar](256) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);

	ALTER TABLE [perms].[RoleMemberships]  WITH CHECK ADD CONSTRAINT [FK_Perms_RoleMemberships_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.Roles') IS NULL
BEGIN
	CREATE TABLE [perms].[Roles](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Roles] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[RoleName] [nvarchar](128) NOT NULL,
		[RoleType] [char](1) NOT NULL,
		[RoleTypeDesc] [nvarchar](60) NOT NULL,
		[DefaultSchema] [nvarchar](128) NULL
	);

	ALTER TABLE [perms].[Roles]  WITH CHECK ADD  CONSTRAINT [FK_Perms_Roles_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.SchemaPermissions') IS NULL
BEGIN
	CREATE TABLE [perms].[SchemaPermissions](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Schema_Permissions] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[SchemaName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);

	ALTER TABLE [perms].[SchemaPermissions]  WITH CHECK ADD  CONSTRAINT [FK_Perms_SchemaPermissions_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.Users') IS NULL
BEGIN
	CREATE TABLE [perms].[Users](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_Users] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[UserName] [nvarchar](256) NOT NULL,
		[UserType] [char](1) NOT NULL,
		[UserTypeDesc] [nvarchar](60) NOT NULL,
		[DefaultSchema] [nvarchar](128) NULL,
		[LoginName] [nvarchar](128) NOT NULL,
		[LoginType] [char](1) NOT NULL,
		[isDisabled] [bit] NOT NULL,
		[SID] [varbinary](85) NULL,
		[PasswordHash] [varbinary](256) NULL
	);
	
	ALTER TABLE [perms].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Perms_Users_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


IF OBJECT_ID('perms.ServerPermissions') IS NULL
BEGIN
	CREATE TABLE [perms].[ServerPermissions](
		[ID] [bigint] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Perms_ServerPermissions] PRIMARY KEY CLUSTERED ,
		[SnapshotID] [bigint] NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[PermissionTypeDesc] [nvarchar](60) NOT NULL,
		[LoginName] [nvarchar](128) NOT NULL
	);

	ALTER TABLE [perms].[ServerPermissions]  WITH CHECK ADD  CONSTRAINT [FK_Perms_ServerPermissions_Snapshot] FOREIGN KEY([SnapshotID]) REFERENCES [perms].[Snapshots] ([ID]);
END
GO


/**************************************************************************
	Create Stored Procedures
***************************************************************************/

--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.createDatabaseSnapshot') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.createDatabaseSnapshot AS	SELECT 1;';
GO

ALTER PROCEDURE [perms].[createDatabaseSnapshot]
(
	@DBName	NVARCHAR(128)
)
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to create a snapshot of the current permissions in a given database

	Parameters:
			@DBName - REQUIRED - Name of the Database you want to create a Permissions Snapshot for.

	Usage:	
			--Take a Permissions Snapshot for the MyDB database
			EXEC [perms].[createDatabaseSnapshot] @DBName='MyDB';
***************************************************************************/

BEGIN
	SET NOCOUNT ON;
	
	DECLARE @SnapshotID BIGINT;
	DECLARE @CRLF NCHAR(2) = NCHAR(13)+NCHAR(10);

	INSERT INTO [perms].[Snapshots] (DatabaseName) VALUES (@DBName);
	SELECT @SnapshotID = SCOPE_IDENTITY();

	DECLARE @SQLStmt NVARCHAR(MAX);
	SELECT @SQLStmt = N'USE ' + QUOTENAME(@DBName) +';
	' + @CRLF

	/* 
		##-Users-##
	*/
	CREATE TABLE #Users(
		[UserName] [nvarchar](128) NOT NULL,
		[UserType] [char](1) NOT NULL,
		[UserTypeDesc] [nvarchar](60) NOT NULL,
		[DefaultSchema] [nvarchar](128) NULL,
		[LoginName] [nvarchar](128) NOT NULL,
		[LoginType] [char](1) NOT NULL,
		[isDisabled] [bit] NOT NULL,
		[SID] [varbinary](85) NULL,
		[PasswordHash] [varbinary](256) NULL
	);

	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #Users([UserName], [UserType], [UserTypeDesc], [DefaultSchema], [LoginName], [LoginType], [isDisabled], [SID], [PasswordHash])
	SELECT dp.name
		,dp.type
		,dp.type_desc
		,dp.default_schema_name
		,sp.name
		,sp.type
		,sp.is_disabled
		,sp.sid
		,l.password_hash
	FROM sys.database_principals dp
	JOIN sys.server_principals sp on dp.sid = sp.sid
	LEFT JOIN sys.sql_logins l on l.principal_id = sp.principal_id 
	WHERE dp.type_desc IN (''WINDOWS_GROUP'',''WINDOWS_USER'',''SQL_USER'')
	AND dp.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'');
	' + @CRLF
	

	/* 
		##-Database Roles-##
	*/
	CREATE TABLE #Roles(
		[RoleName] [nvarchar](128) NOT NULL,
		[RoleType] [char](1) NOT NULL,
		[RoleTypeDesc] [nvarchar](60) NOT NULL,
		[DefaultSchema] [nvarchar](128) NULL
	);

	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #Roles ([RoleName], [RoleType], [RoleTypeDesc], [DefaultSchema])
	SELECT name
		,type
		,type_desc
		,default_schema_name
	FROM sys.database_principals
	WHERE type_desc IN (''DATABASE_ROLE'',''APPLICATION_ROLE'')
	AND is_fixed_role = 0
	AND principal_id <> 0;
	' + @CRLF


	/* 
		##-Role Memberships-##
	*/
	CREATE TABLE #RoleMemberships(
		[RoleName] [nvarchar](256) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);
	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #RoleMemberships([RoleName], [UserName])
	SELECT USER_NAME(role_principal_id)
		,USER_NAME(member_principal_id)
	FROM sys.database_role_members;
	' + @CRLF


	/* 
		##-Object permissions - GRANT, DENY, REVOKE statements-##
	*/
	CREATE TABLE #ObjectPermissions(
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[SchemaName] [nvarchar](128) NOT NULL,
		[ObjectName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL,
		[ClassDesc] [nvarchar](60) NOT NULL,
		[ColumnName] [nvarchar](128) NULL
	);
	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #ObjectPermissions ([State], [StateDesc], [PermissionName], [SchemaName], [ObjectName], [UserName], [ClassDesc], [ColumnName])
	SELECT perm.state -- D (DENY), R (REVOKE), G (GRANT), W (GRANT_WITH_GRANT_OPTION)
		,perm.state_desc -- actual state command for D, R, G, W
		,perm.permission_name
		,SCHEMA_NAME(obj.schema_id)
		,obj.name
		,USER_NAME(perm.grantee_principal_id)
		,perm.class_desc
		,cl.name
	FROM sys.database_permissions AS perm
	INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id]
	LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
	WHERE perm.class_desc = ''OBJECT_OR_COLUMN'';
	' + @CRLF


	/* 
		##-Schema assignments - GRANT, DENY, REVOKE statements-##
	*/

	CREATE TABLE #SchemaPermissions(
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[SchemaName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);
	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #SchemaPermissions ([State], [StateDesc], [PermissionName], [SchemaName], [UserName])
	SELECT perm.state
		,perm.state_desc
		,perm.permission_name
		,SCHEMA_NAME(major_id)
		,USER_NAME(grantee_principal_id)
	FROM sys.database_permissions perm
	WHERE class_desc = ''SCHEMA'';
	' + @CRLF


	/* 
		##-Database permissions - GRANT, DENY, REVOKE-##
	*/

	CREATE TABLE #DatabasePermissions(
		[State] [char](1) NOT NULL,
		[StateDesc] [nvarchar](60) NOT NULL,
		[PermissionName] [nvarchar](128) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	);

	SELECT @SQLStmt = @SQLStmt + N'
	INSERT INTO #DatabasePermissions ([State], [StateDesc], [PermissionName], [UserName])
	SELECT perm.state -- D (DENY), R (REVOKE), G (GRANT), W (GRANT_WITH_GRANT_OPTION)
		,perm.state_desc -- actual state command for D, R, G; GRANT_WITH_GRANT_OPTION for W
		,perm.permission_name
		,USER_NAME(perm.grantee_principal_id)
	FROM sys.database_permissions AS perm
	WHERE class_desc = ''DATABASE'';
	' + @CRLF

	--PRINT @SQLStmt
	EXECUTE sp_executesql @SQLStmt

	
	/* 
		##-Load Database permissions into real tables-##
	*/

	INSERT INTO [perms].[Users]([SnapshotID], [UserName], [UserType], [UserTypeDesc], [DefaultSchema], [LoginName], [LoginType], [isDisabled], [SID], [PasswordHash])
	SELECT @SnapshotID, [UserName], [UserType], [UserTypeDesc], [DefaultSchema], [LoginName], [LoginType], [isDisabled], [SID], [PasswordHash] FROM #Users;
	
	INSERT INTO [perms].[Roles] ([SnapshotID], [RoleName], [RoleType], [RoleTypeDesc], [DefaultSchema])
	SELECT @SnapshotID, [RoleName], [RoleType], [RoleTypeDesc], [DefaultSchema] FROM #Roles;

	INSERT INTO [perms].[RoleMemberships]([SnapshotID], [RoleName], [UserName])
	SELECT @SnapshotID, [RoleName], [UserName] FROM #RoleMemberships;

	INSERT INTO [perms].[ObjectPermissions] ([SnapshotID], [State], [StateDesc], [PermissionName], [SchemaName], [ObjectName], [UserName], [ClassDesc], [ColumnName])
	SELECT @SnapshotID, [State], [StateDesc], [PermissionName], [SchemaName], [ObjectName], [UserName], [ClassDesc], [ColumnName] FROM #ObjectPermissions;

	INSERT INTO [perms].[SchemaPermissions] ([SnapshotID], [State], [StateDesc], [PermissionName], [SchemaName], [UserName])
	SELECT @SnapshotID, [State], [StateDesc], [PermissionName], [SchemaName], [UserName] FROM #SchemaPermissions;

	INSERT INTO [perms].[DatabasePermissions] ([SnapshotID], [State], [StateDesc], [PermissionName], [UserName])
	SELECT @SnapshotID, [State], [StateDesc], [PermissionName], [UserName] FROM #DatabasePermissions;

	DROP TABLE #Users;
	DROP TABLE #Roles;
	DROP TABLE #RoleMemberships;
	DROP TABLE #ObjectPermissions;
	DROP TABLE #SchemaPermissions;
	DROP TABLE #DatabasePermissions;

END
GO

--If the old procedure exists, modify it to call the new procedure.
IF OBJECT_ID('perms.createSnapshot') IS NOT NULL
EXEC sp_executesql N'
  ALTER PROCEDURE [perms].[createSnapshot]
  AS
  BEGIN
  /*
	The [perms].[createSnapshot] procedure has been depricated, use [perms].[createDatabaseSnapshot] instead
  */
  EXEC [perms].[createDatabaseSnapshot] @DBName = @DBName;
  END;';

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.snapshotAllDatabases') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.snapshotAllDatabases AS	SELECT 1;';
GO

ALTER PROCEDURE [perms].[snapshotAllDatabases]
	@IncludeSysDatabases bit = 1
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to create a snapshot of the current permissions in all databases on a server.

	Parameters:
			NONE

	Usage:	
			--Take a Permissions Snapshot for all databases.
			EXEC [perms].[snapshotAllDatabases];
***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	DECLARE @tmpDatabases TABLE (
				ID INT IDENTITY PRIMARY KEY
				,DatabaseName NVARCHAR(128)
				,Completed BIT
			);

	DECLARE @CurrentID INT;
	DECLARE @CurrentDatabaseName NVARCHAR(128);

	IF @IncludeSysDatabases = 1
		BEGIN
			INSERT INTO @tmpDatabases (DatabaseName, Completed)
			SELECT [Name], 0
			FROM sys.databases
			WHERE state = 0
			AND source_database_id IS NULL
			ORDER BY [Name] ASC
		END
		ELSE BEGIN
			INSERT INTO @tmpDatabases (DatabaseName, Completed)
			SELECT [Name], 0
			FROM sys.databases
			WHERE state = 0 AND database_id > 4
			AND source_database_id IS NULL
			ORDER BY [Name] ASC
		END

	WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
	BEGIN
		SELECT TOP 1 @CurrentID = ID,
					 @CurrentDatabaseName = DatabaseName
		FROM @tmpDatabases
		WHERE Completed = 0
		ORDER BY ID ASC

		EXEC [perms].[createDatabaseSnapshot] @DBName = @CurrentDatabaseName

		-- Update that the database is completed
		UPDATE @tmpDatabases
		SET Completed = 1
		WHERE ID = @CurrentID

		-- Clear variables
		SET @CurrentID = NULL
		SET @CurrentDatabaseName = NULL
	END
END
GO

--If the old procedure exists, modify it to call the new procedure.
IF OBJECT_ID('perms.snapshotAllDBs') IS NOT NULL
EXEC sp_executesql N'
  ALTER PROCEDURE [perms].[snapshotAllDBs]
  AS
  BEGIN
  /*
	The [perms].[snapshotAllDBs] procedure has been depricated, use [perms].[snapshotAllDatabases] instead
  */
  EXEC [perms].[snapshotAllDatabases];
  END;';

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.createSeverSnapshot') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.createSeverSnapshot AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[createSeverSnapshot]

AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017-2018 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to create a snapshot of the current Server level permissions on this SQL Server

	Parameters:
			NONE

	Usage:	
			--Take a Permissions Snapshot of this SQL Sever
			EXEC [perms].[createSeverSnapshot];
***************************************************************************/

BEGIN

	SET NOCOUNT ON;
	
	DECLARE @SnapshotID BIGINT;

	INSERT INTO [perms].[Snapshots] (DatabaseName) VALUES (N'{Server-Permissions}');
	SELECT @SnapshotID = SCOPE_IDENTITY();
	
	--server level roles	
	INSERT INTO [perms].[ServerPermissions]
           ([SnapshotID]
           ,[PermissionName]
           ,[PermissionTypeDesc]
           ,[LoginName])
	SELECT @SnapshotID
			,r.name
			,r.type_desc
			,m.name
	FROM sys.server_role_members  rm
	INNER JOIN sys.server_principals AS r ON rm.role_principal_id = r.principal_id  
	INNER JOIN sys.server_principals AS m ON rm.member_principal_id = m.principal_id
	WHERE rm.member_principal_id > 1

	--server level permissions	
	INSERT INTO [perms].[ServerPermissions]
           ([SnapshotID]
           ,[PermissionName]
           ,[PermissionTypeDesc]
           ,[LoginName])
	SELECT @SnapshotID
			,SrvPerm.state_desc +' '+ SrvPerm.permission_name
			,N'SERVER_PERMISSION'
			,SP.name	
	FROM sys.server_permissions AS SrvPerm 
	INNER JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
	WHERE   SP.type IN ( 'S', 'U', 'G' ) 
	AND SP.name NOT LIKE '##%##'
	AND SP.name NOT LIKE 'NT AUTHORITY%'
	AND SP.name NOT LIKE 'NT SERVICE%'
	AND SP.name <> ('sa');

END
GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.applyPermissions') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.applyPermissions AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[applyPermissions]
	@DBName NVARCHAR(128),
	@SnapshotID INT = NULL,
	@User NVARCHAR(256) = NULL,
	@CreateLogins BIT = 1,
	@ExecuteScript BIT = 0,	
	@CopySID BIT = 1, --Copies the SID of a SQL user
	@DestinationDatabase  NVARCHAR(128) = NULL,
	@AltUserNames XML = NULL
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to apply a Permissions Snapshot to a specified database; 
			If a Snapshot ID is specified, it will restore that Snapshot, 
			otherwise it defaults to the most recent Snapshot for the specified database;

	Parameters:
			@DBName - REQUIRED - Name of the Database you want to apply a Permissions Snapshot to.
			@SnapshotID - OPTIONAL - ID of the specific Snapshot you want to apply.
			@User - OPTIONAL - User Name of a specific user you want permissions applied for.
			@CreateLogins - OPTIONAL - Flag for whether or not to generate the CREATE LOGIN scripts for the user(s) listed in the Permissions Snapshot.
			@ExecuteScript - OPTIONAL -	Flag for whether or not to actually apply the Permissions Snapshot.
										If 1: Automatically apply the permissions to the databse.
										If 0: Generates the permissions script to be reviewed/run manually.
			@CopySID - OPTIONAL - Generates the SID of a SQL user as part of the script
			@DestinationDatabase - OPTIONAL - Database to apply Permissions Snapshot to; defaults to the specified @DBName.
			@AltUserNames - OPTIONAL - Used for cloning a specified user's permissions to a new user. (see [perms].[clonePermissions] procedure)
				VALUE FOR @AltUserNames = 
				<altusers>
					<user>
						<original>OriginalUser</original>	-- REQUIRED. original UserName, as found in the perms.Users table
						<new>NewUser</new>			-- REQUIRED. new UserName. should include domain name if appropriate - e.g. HCA\ibm8561
						<DefaultSchema>dbo</DefaultSchema>	-- OPTIONAL. default schema
						<LoginName></LoginName>		-- OPTIONAL. login name. Defaults to new UserName
						<LoginType></LoginType>	-- OPTIONAL, defaults to U. S = SQL User, U = Windows User, G = Windows Group
					</user>
				</altusers>

	Usage:	
			--Apply the most recent Permissions Snapshot to the MyDB database.
			EXEC [perms].[applyPermissions] @DBName='MyDB';

***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLSTMT NVARCHAR(4000);
	DECLARE @SQLSTMT2 NVARCHAR(4000);
	DECLARE @VSnapshotID INT = NULL;

	DECLARE @CRLF NCHAR(2) = NCHAR(13) + NCHAR(10);

	-- Create Temp Table
	CREATE TABLE #SQLResults
	(
		ID		INT IDENTITY(1,1) NOT NULL,
		STMT	NVARCHAR(1000)	NOT NULL
	)

	-- Determine Correct Snapshot ID
	IF @SnapshotID IS NULL
		SELECT TOP 1 @VSnapshotID = [ID] FROM [perms].[Snapshots] WHERE [DatabaseName] = @DBName ORDER BY [CaptureDate] DESC;
	ELSE
		SELECT TOP 1 @VSnapshotID = [ID] FROM [perms].[Snapshots] WHERE [DatabaseName] = @DBName AND [ID] = @SnapshotID; --ORDER BY [CaptureDate] DESC

	IF @VSnapshotID IS NULL -- STILL???
	BEGIN
		RAISERROR(N'No Valid Snapshot Available',16,1);
		RETURN;
	END

	IF @DestinationDatabase IS NULL
		SET @DestinationDatabase = @DBName;

	-- Setup Alternate UserNames Capability
	CREATE TABLE #AltUsers (
		OriginalUser NVARCHAR(256) NOT NULL,
		NewUser NVARCHAR(256) NOT NULL,
		DefaultSchema NVARCHAR(128) NULL,
		LoginName NVARCHAR(128) NULL,
		LoginType CHAR(1) NULL DEFAULT 'U'
	);

	IF @AltUserNames IS NOT NULL
	BEGIN
		INSERT INTO #AltUsers (OriginalUser, NewUser, DefaultSchema, LoginName, LoginType)
		SELECT Tbl.Col.value('original[1]', 'sysname'),
			Tbl.Col.value('new[1]','sysname'),
			Tbl.Col.value('DefaultSchema[1]','sysname'),
			Tbl.Col.value('LoginName[1]','sysname'),
			Tbl.Col.value('logintype[1]','char(1)')
		FROM @AltUserNames.nodes('/altusers/user') Tbl(Col);
	END;

	INSERT INTO #AltUsers (OriginalUser, NewUser, DefaultSchema, LoginName, LoginType)
	SELECT UserName, UserName, DefaultSchema, LoginName, LoginType
	FROM perms.Users
	WHERE UserName NOT IN (SELECT OriginalUser FROM #AltUsers)
		AND SnapshotID = @VSnapshotID

	UPDATE #AltUsers
	SET #AltUsers.DefaultSchema = u.DefaultSchema
	FROM #AltUsers
	JOIN perms.Users u ON #AltUsers.OriginalUser = u.UserName
	WHERE #AltUsers.DefaultSchema IS NULL

	UPDATE #AltUsers
	SET LoginName = NewUser
	WHERE LoginName IS NULL

	UPDATE #AltUsers
	SET LoginType = 'U'
	WHERE LoginType IS Null

	INSERT INTO #SQLResults (STMT)
	SELECT '-- Database: ' + @DBName
	INSERT INTO #SQLResults (STMT)
	SELECT '-- Snapshot ID: ' + CAST(@VsnapshotID AS varchar(10))

	INSERT INTO #SQLResults(STMT) VALUES ('')

	INSERT INTO #SQLResults(STMT) VALUES ('USE [' + @DestinationDatabase + '];')

	INSERT INTO #SQLResults(STMT) VALUES ('')


	-- ### LOGINS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### LOGINS ###')

	-- U, S, G
	IF @CreateLogins = 1
	BEGIN
		INSERT INTO #SQLResults (STMT)
		SELECT 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + u.LoginName + ''') '
			+ 'BEGIN '
			+ 'CREATE LOGIN ' + QUOTENAME(u.LoginName)
			+ CASE
				WHEN u.LoginType = 'U' THEN ' FROM WINDOWS '
				WHEN u.LoginType = 'G' THEN ' FROM WINDOWS '
				ELSE ' WITH PASSWORD = ' + CONVERT(VARCHAR(MAX), p.[PasswordHash], 1) + ' HASHED'
			  END
			+ CASE 
				WHEN @CopySID = 1 AND u.LoginType = 'S' THEN ', SID=' + CONVERT(varchar(max), p.SID, 1)-- ALTER LOGIN ' + QUOTENAME(LoginName) + ' DISABLE '
				ELSE ''
			  END
			+ ' END'
		FROM #AltUsers u
		INNER JOIN [perms].[Users] p ON p.LoginName = u.OriginalUser
		WHERE (@User IS NULL OR u.OriginalUser = @User)
		AND p.SnapshotID = @VSnapshotID;

		INSERT INTO #SQLResults (STMT)
		SELECT 'ALTER LOGIN ' + QUOTENAME(LoginName) + ' DISABLE'
		FROM [perms].[Users] u
		WHERE u.SnapshotID = @VSnapshotID
			AND (u.isDisabled = 1)
			AND (@User IS NULL OR u.UserName = @User);

	END;

	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### REPAIR EXISTING USERS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### REPAIR USERS ###')

	INSERT INTO #SQLResults (STMT)
	SELECT 'IF EXISTS (SELECT * FROM sys.database_principals dp '
		+ 'LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid '
		+ 'WHERE dp.type = ''S'' '
		+ 'AND sp.sid IS NULL '
		+ 'AND dp.name = N' + QUOTENAME(NewUser,'''') + ') '
		+ 'EXEC sp_change_users_login ''Auto_Fix'', ' + QUOTENAME(NewUser,'''') + ';'
	FROM #AltUsers u
	WHERE (@User IS NULL OR u.OriginalUser = @User)
		AND u.LoginType = 'S';

	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### USERS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### USERS ###')

	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + QUOTENAME(NewUser,'''') + ') '
		+ 'CREATE USER ' + QUOTENAME(NewUser) + ' FOR LOGIN ' + QUOTENAME(LoginName)
		+ CASE
			WHEN DefaultSchema IS NOT NULL THEN	' WITH DEFAULT_SCHEMA=' + QUOTENAME(DefaultSchema)
			ELSE ''
		  END
		+ ';'
	FROM #AltUsers u
	WHERE (@User IS NULL OR u.OriginalUser = @User);

	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### ROLES ###
	/* First things first, we need to put the roles into #AltUsers so that when
	we do the actual permissions, they are there */
	INSERT INTO #AltUsers (OriginalUser, NewUser)
	SELECT RoleName, RoleName
	FROM perms.Roles
	WHERE SnapshotID = @VSnapshotID

	-- First, do Database Roles
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### ROLES ###')
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N' + QUOTENAME(RoleName,'''') + ' AND type = ''R'') '
		+ 'CREATE ROLE ' + QUOTENAME(RoleName) + ' AUTHORIZATION [dbo]'
	FROM [perms].[Roles] r
	WHERE r.SnapshotID = @VSnapshotID
		AND r.RoleType = 'R'
		AND (@User IS NULL OR r.RoleName = @User);

	-- Then, do Application Roles.  Note, doesn't transfer password
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + QUOTENAME(RoleName) + ''' AND type = ''A'') '
		+ 'CREATE APPLICATION ROLE ' + QUOTENAME(RoleName) + ' WITH PASSWORD = ''Healthtrust123'' '
		+ CASE 
			WHEN DefaultSchema IS NOT NULL THEN	', DEFAULT_SCHEMA=' + QUOTENAME(DefaultSchema)
			ELSE ''
		  END
	FROM [perms].[Roles] r
	WHERE r.SnapshotID = @VSnapshotID
		AND r.RoleType = 'A'
		AND (@User IS NULL);


	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### ROLE ASSIGNMENTS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### ROLE ASSIGNMENTS ###')
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF IS_ROLEMEMBER(' + QUOTENAME(RoleName,'''') + ',' + QUOTENAME(au.NewUser,'''') + ') = 0 '
		+ 'EXEC sp_addrolemember @RoleName = ' + QUOTENAME(RoleName,'''') + ', @membername = ' + QUOTENAME(au.NewUser,'''')
	FROM [perms].[RoleMemberships] rm
	JOIN #AltUsers au ON rm.UserName = au.OriginalUser
	WHERE rm.SnapshotID = @VSnapshotID
		AND (@User IS NULL OR rm.UserName = @User);

	INSERT INTO #SQLResults(STMT) VALUES ('')


	-- ### OBJECT PERMISSIONS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### OBJECT PERMISSIONS ###')
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_permissions '
		+ 'WHERE class_desc = ''OBJECT_OR_COLUMN'' '
		+ 'AND grantee_principal_id = DATABASE_PRINCIPAL_ID(' + QUOTENAME(au.NewUser,'''') + ') '
		+ 'AND Permission_Name = ' + QUOTENAME(PermissionName,'''')
		+ ' AND State_Desc = ' + QUOTENAME(StateDesc,'''')
		+ ' AND major_id = OBJECT_ID(N' + QUOTENAME(ObjectName,'''') + ') '
		+ CASE
			WHEN ColumNname IS NULL THEN SPACE(0)
			ELSE 'AND minor_id = columnproperty(object_id(N''' + SchemaName + '.' + ObjectName + '''),N''' + ColumNname + ''', ''columnid'') '
		  END
		+ ') '
		+ CASE
			WHEN [State]<> 'W' THEN StateDesc + SPACE(1)
			ELSE 'GRANT '
		  END
		+ PermissionName 
		+ ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName)
		+  CASE
			WHEN ColumNname IS NULL THEN SPACE(1)
			ELSE ' (' + QUOTENAME(ColumNname) + ')'
		   END
		+ 'TO ' + QUOTENAME(au.NewUser)
		+ CASE
			WHEN [State]<> 'W' THEN SPACE(0)
			ELSE ' WITH GRANT OPTION'
		  END
	FROM [perms].[ObjectPermissions] op
		JOIN #AltUsers au ON op.UserName = au.OriginalUser
	WHERE op.SnapshotID = @VSnapshotID
		AND (@User IS NULL OR op.UserName = @User);

	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### SCHEMA PERMISSIONS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### SCHEMA PERMISSIONS ###')
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_permissions '
		+ 'WHERE class_desc = ''SCHEMA'' '
		+ 'AND grantee_principal_id = DATABASE_PRINCIPAL_ID(' + QUOTENAME(au.NewUser,'''') + ') '
		+ 'AND Permission_Name = ' + QUOTENAME(PermissionName,'''')
		+ ' AND State_Desc = ' + QUOTENAME(StateDesc,'''')
		+ ' AND major_id = SCHEMA_ID(N' + QUOTENAME(SchemaName,'''') + ')) '
		+ CASE
			WHEN [State]<> 'W' THEN StateDesc + SPACE(1)
			ELSE 'GRANT '
		  END
		+ PermissionName 
		+ ' ON SCHEMA :: ' + SchemaName
		+ ' TO ' + QUOTENAME(au.NewUser)
		+ CASE
			WHEN [State]<> 'W' THEN ';'
			ELSE ' WITH GRANT OPTION;'
		  END
	FROM [perms].[SchemaPermissions] sp
		JOIN #AltUsers au ON sp.UserName = au.OriginalUser
	WHERE sp.SnapshotID = @VSnapshotID
		AND (@User IS NULL OR sp.UserName = @User);

	INSERT INTO #SQLResults(STMT) VALUES ('')

	-- ### DATABASE PERMISSIONS ###
	INSERT INTO #SQLResults(STMT) VALUES ('-- ### DATABASE PERMISSIONS ###')
	INSERT INTO #SQLResults (STMT)
	SELECT 'IF NOT EXISTS (SELECT * FROM sys.database_permissions '
		+ 'WHERE class_desc = ''DATABASE'' '
		+ 'AND grantee_principal_id = DATABASE_PRINCIPAL_ID(' + QUOTENAME(au.NewUser,'''') + ') '
		+ 'AND Permission_Name = ' + QUOTENAME(PermissionName,'''')
		+ ' AND State_Desc = ' + QUOTENAME(StateDesc,'''')
		+ ' AND major_id = 0) '
		+ CASE
			WHEN [State]<> 'W' THEN StateDesc + SPACE(1)
			ELSE 'GRANT '
		  END
		+ PermissionName 
		+ ' TO ' + QUOTENAME(au.NewUser)
		+ CASE
			WHEN [State]<> 'W' THEN ';'
			ELSE ' WITH GRANT OPTION;'
		  END
	FROM [perms].[DatabasePermissions] dp
		JOIN #AltUsers au ON dp.UserName = au.OriginalUser
	WHERE dp.SnapshotID = @VSnapshotID
		AND (@User IS NULL OR dp.UserName = @User);

	--If @executeScript = 0, return the statements.
	IF @ExecuteScript = 0
	BEGIN
		SELECT 
			CAST((STUFF
			(
				(
					SELECT @CRLF + STMT
					FROM #SQLResults
					ORDER BY ID
					FOR XML PATH(''), TYPE
				).value('.[1]','NVARCHAR(MAX)'), 1, 2, '')
			) AS XML) AS sqlSTMT

	END
	ELSE
	BEGIN
		DECLARE @sqlSTMT_prep NVARCHAR(4000)
		DECLARE sql_cursor CURSOR LOCAL FAST_FORWARD FOR
		SELECT STMT FROM #SQLResults 
		WHERE STMT <> '' AND STMT NOT LIKE 'USE %' AND STMT NOT LIKE '--%'
		ORDER BY ID
	
		OPEN sql_cursor
		FETCH NEXT FROM sql_cursor INTO @sqlSTMT
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @sqlSTMT_prep = 'USE [' + @DestinationDatabase + ']; ';
			SELECT @sqlSTMT_prep = @sqlSTMT_prep + @sqlSTMT;
			exec sp_ExecuteSQL @sqlSTMT_prep;
			--SELECT @sqlSTMT_prep;
		
			FETCH NEXT FROM sql_cursor INTO @sqlSTMT
		END
	
		CLOSE sql_cursor
		DEALLOCATE sql_cursor
	END

	DROP TABLE #SQLResults;
	DROP TABLE #AltUsers;

END

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.clonePermissions') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.clonePermissions AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[clonePermissions]
	@UserName NVARCHAR(256)
	,@NewUser NVARCHAR(256)
	,@logintype CHAR(1) = 'U'
	,@CopySID BIT = 0
	,@CreateLogins BIT = 1
	,@ExecuteScript BIT = 0
		
AS 

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to copy all of the permissions from a given user and assign those permissions to another user.
			It will do this for every database on a server, so if a user has permissions on 3 databases, the new user and permissions will be added to those 3 databases.

	Parameters:
			@UserName - REQUIRED - the user we want to clone
			@NewUser - REQUIRED - the user name of the new user we want to create
			@logintype - OPTIONAL - defaults to U. S = SQL User, U = Windows User, G = Windows Group	
			@CopySID - OPTIONAL - Copies the SID of a SQL user
			@CreateLogins - OPTIONAL - Flag for whether or not to generate the CREATE LOGIN scripts for the user(s) listed in the Permissions Snapshot.
			@ExecuteScript - OPTIONAL -	Flag for whether or not to actually apply the Permissions Snapshot.
										If 1: Automatically apply the permissions to the databse.
										If 0 (Default): Generates the permissions script to be reviewed/run manually.

	Usage:	
			--Generate a script to create User2 with all of the permissions that User1 has, but do not automatically run the generated script;
			EXEC [perms].[clonePermissions] @UserName = 'user1', @NewUser = 'user2', @CreateLogins = 1, @ExecuteScript = 0

***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(4000)
			,@db NVARCHAR(128)
	-- List the DBs this user has access to, based on the most recent Snapshots
	SELECT DISTINCT
			[ID] = s1.ID,
			[DB] = databasename,
			[CaptureDate]
	INTO    #CurrentSnaps
	FROM    perms.Snapshots s1
			INNER JOIN perms.RoleMemberships rm ON rm.SnapshotID = s1.ID
			INNER JOIN sys.databases db ON db.name = s1.databasename and state_desc = 'ONLINE'--only return active, online databases
	WHERE   rm.username = @UserName
			AND CaptureDate IN ( SELECT    MAX(CaptureDate)
							  FROM      perms.Snapshots s2
							  WHERE     s2.databasename = s1.databasename )
	ORDER BY [DB];

	--SELECT * FROM #CurrentSnaps

	--IF there are databases found, we need to add the new user to them
	IF (SELECT COUNT(*) FROM #CurrentSnaps) > 0
	BEGIN
		--Create an XML string to pass to the permissions proc; this tells it what user we want to clone the new user as.
		DECLARE @AltUsernames XML = '<altusers><user><original>'+@UserName+'</original><new>'+@NewUser+'</new><logintype>'+@logintype+'</logintype></user></altusers>'

		--In case our user has permission on more than 1 database, we're going to loop through the list to make sure we get them all.
		DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT DB FROM #CurrentSnaps
		OPEN cur

		FETCH NEXT FROM cur INTO @db

		WHILE @@FETCH_STATUS = 0 
		BEGIN
			-- 1) Write our SQL string that calls the PROC that actually applies the permissions.
			SET @SQL = '
			EXEC [perms].[applyPermissions]
				@DBName = ['+@db+'],
				@User = ['+@UserName+'],
				@CreateLogins = '+CAST(@CreateLogins AS CHAR(1))+',
				@ExecuteScript = '+CAST(@ExecuteScript AS CHAR(1))+',
				@CopySID = '+CAST(@CopySID AS CHAR(1))+',
				@AltUsernames = '''+CAST(@AltUsernames AS NVARCHAR(500))+'''
			'
			--make sure the DB is there, we've had cases where dabases got snapshotted, but were later deleted. 
			IF DB_ID(@db) IS NOT NULL
			BEGIN
				-- 2) now run the command to call the PROC listed above
				EXEC sp_executesql @SQL	
			END

			FETCH NEXT FROM cur INTO @db
		END
		CLOSE cur    
		DEALLOCATE cur

	END

	DROP TABLE #CurrentSnaps

END

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.purgeSnapshots') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.purgeSnapshots AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[purgeSnapshots]
	@DaysToKeep INT = 90
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to purge old Permission Snapshots from the database

	Parameters:
			@DaysToKeep - REQUIRED - How many days worth of data do you want to keep?  Anything older than this number (defualts to 90 days) will be deleted. 

	Usage:	
			--Delete all snapshots older than 90 days (default);
			EXEC [perms].[purgeSnapshots];

			--Delete all snapshots older than 120 days;
			EXEC [perms].[purgeSnapshots] @DaysToKeep = 120;

***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	BEGIN TRANSACTION

		BEGIN TRY

		DECLARE @snapshots TABLE ([SnapshotID] INT PRIMARY KEY CLUSTERED NOT NULL);
		
		--create a list of the snapshots to delete
		INSERT INTO @snapshots ([SnapshotID])
		SELECT ID FROM [perms].[Snapshots] ss
		WHERE ss.[CaptureDate] < DATEADD(day,0-@DaysToKeep,GETDATE())

		--go forth and delete
		DELETE p
		FROM [perms].[DatabasePermissions] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];

		DELETE p
		FROM [perms].[SchemaPermissions] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];
		
		DELETE p
		FROM [perms].[ObjectPermissions] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];
		
		DELETE p
		FROM [perms].[RoleMemberships] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];
		
		DELETE p
		FROM [perms].[Roles] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];
		
		DELETE p
		FROM [perms].[Users] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];
		
		DELETE p
		FROM [perms].[Snapshots] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[ID];

		DELETE p
		FROM [perms].[ServerPermissions] p
		INNER JOIN @snapshots s ON s.[SnapshotID] = p.[SnapshotID];


		COMMIT TRANSACTION
		END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.removeAllUsersFromDB') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.removeAllUsersFromDB AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[removeAllUsersFromDB] (
	@DBName NVARCHAR(128)
 )
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to drop all users from a database.
			Users that own certificates, and default system users, will not be dropped.
            Users can be added back using the latest permissions snapshot for that database.

	Parameters:
			@DBName - REQUIRED - Database you wish to remove all user from.

	Usage:	
			--Remove all users from the MyDB database;
			EXEC [perms].[removeAllUsersFromDB] @DBName='MyDB';
			
***************************************************************************/

BEGIN
	SET NOCOUNT ON;
    DECLARE @Message NVARCHAR(255);
    DECLARE @sql NVARCHAR(4000);
    DECLARE @Error NVARCHAR(400);

    --ensure parameters supplied are compatible.
    SET @Error = 0;
    IF @DBName = ''
    BEGIN
		SET @Message = 'The value for parameter @DBName is not supported.' + CHAR(13) + CHAR(10)
		RAISERROR(@Message,16,1) WITH NOWAIT
		SET @Error = @@ERROR
    END

    SET @sql = N'USE ' + @DBName + ';
	'

    SET @sql = @sql + N'
			DECLARE @UserID       varchar(128)
			DECLARE @SQLstmt      varchar(255)

			PRINT ''Fix Database Users''
			PRINT ''Server:   '' + @@servername
			PRINT ''Database: '' + DB_NAME()
			
			--clean up schemas before trying to drop users			
			DECLARE SchemaResetCursor CURSOR LOCAL FAST_FORWARD FOR
			SELECT name 
			FROM sys.schemas
			WHERE SCHEMA_ID BETWEEN 5 AND 1000
			AND principal_id <> 1


			OPEN SchemaResetCursor
			FETCH NEXT FROM SchemaResetCursor INTO @UserID
			WHILE @@FETCH_STATUS = 0
				BEGIN
					IF EXISTS (SELECT * FROM sys.schemas WHERE name = @UserID)
					BEGIN
						SELECT @SQLstmt = ''ALTER AUTHORIZATION ON SCHEMA:: ['' + @UserID + ''] TO dbo;''
						PRINT @SQLstmt
						EXEC (@SQLstmt)
					END
				FETCH NEXT FROM SchemaResetCursor INTO @UserID
				END

			CLOSE SchemaResetCursor
			DEALLOCATE SchemaResetCursor


			--avoid dropping users that were creating using certificates
			DECLARE DropUserCursor CURSOR LOCAL FAST_FORWARD FOR
			SELECT p.name FROM sys.database_principals p
			LEFT JOIN sys.certificates c ON p.principal_id = c.principal_id
			WHERE p.type <> ''R''
			AND p.principal_id >=5
			AND c.principal_id IS NULL

			OPEN DropUserCursor
			FETCH NEXT FROM DropUserCursor INTO @UserID
			WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @SQLstmt = ''exec sp_revokedbaccess '''''' + @UserID + ''''''''
				PRINT @SQLstmt
				EXEC (@SQLstmt)
				FETCH NEXT FROM DropUserCursor INTO @UserID
			END

			CLOSE DropUserCursor
			DEALLOCATE DropUserCursor
	   '
   EXECUTE sp_executesql @sql
   --PRINT @sql
 
END
GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.removeLogin') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.removeLogin AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[removeLogin] (
	@UserName NVARCHAR(256),
	@ExecuteScript BIT = 0,
	@DropLogin BIT = 1
)
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to remove a user from all databases, then drop the login for that user.
			Users that own certificates, and default system users, will not be dropped.

	Parameters:
			@UserName - REQUIRED - the user we want to clone
			@ExecuteScript - OPTIONAL -	Flag for whether or not to actually apply the Permissions Snapshot.
										If 1: Automatically apply the permissions to the databse.
										If 0 (Default): Generates the permissions script to be reviewed/run manually.
			@DropLogin - OPTIONAL - Flag for where or not to actually drop the login after the user has been removed from all databases.

	Usage:	
			--Generate script to remove User1 from all databases and drop login;
			EXEC [perms].[removeLogin] @UserName = N'User1', @ExecuteScript = 0, @DropLogin = 1;
			
***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(4000);
	DECLARE @DBname NVARCHAR(128);

	DECLARE @dblist TABLE ([DBname] NVARCHAR(128));
	DECLARE @protectedUsers TABLE ([username] NVARCHAR(256));

	IF @ExecuteScript = 0
	BEGIN
		--SELECT 'THIS IS ONLY A TEST; THE SCRIPT HAS NOT EXECUTED AND THE USER HAS NOT BEEN REMOVED!'
		PRINT 'THIS IS ONLY A TEST; THE SCRIPT HAS NOT EXECUTED AND THE USER HAS NOT BEEN REMOVED!'
	END

	--get list of users that were creating using certificates
	INSERT INTO @protectedUsers
	SELECT distinct p.name 
	FROM sys.database_principals p
	INNER JOIN sys.certificates c ON p.principal_id = c.principal_id
	WHERE p.type <> 'R'
	
	IF @UserName IN (select coalesce(username,'N/A') from @protectedUsers)
	THROW 51000, 'This user is attached to a certificate and cannot be deleted!', 1;  
	
	--get list of system users
	INSERT INTO @protectedUsers
	SELECT  p.name 
	FROM sys.database_principals p
	WHERE p.type <> 'R'
	AND p.principal_id < 5
	
	IF @UserName IN (select username from @protectedUsers)
	THROW 51000, 'This is a system user and cannot be deleted!', 1;  
 
	--get list of databases this user has access to
	--TODO: I don't like using sp_MSforeachdb, find a replacement query
	SET @sql ='SELECT ''[?]'' AS DBName FROM [?].sys.database_principals WHERE name=''' + @UserName + ''''
	INSERT INTO @dblist
	EXEC sp_MSforeachdb @command1=@sql
		
	IF EXISTS (SELECT DBname FROM @dblist)
		BEGIN
			DECLARE DBList CURSOR LOCAL FAST_FORWARD FOR
			SELECT DBname FROM @dblist

			OPEN DBList
			FETCH NEXT FROM DBList INTO @DBname
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @sql = N'USE ' + @DBName + ';'
				SET @sql = @sql + N'
				DROP USER ['+@UserName+'];'
				PRINT 'Dropping user from ' +	+ @DBName + ';'
		
				IF @ExecuteScript = 1
					EXECUTE sp_executesql @sql
				--ELSE
				--	SELECT @sql

				FETCH NEXT FROM DBList INTO @DBname
			END

			CLOSE DBList
			DEALLOCATE DBList
		
		END
	ELSE PRINT 'User ''' +@UserName+''' Is Not Assigned To Any Databases.'

	--if the login exists, drop it!
	IF @DropLogin = 1 AND EXISTS (select loginname from master.dbo.syslogins where name = @UserName)
		BEGIN
			PRINT 'Dropping Login ' +@UserName+''
			SET @sql = N'DROP LOGIN [' +@UserName+']' 
			IF @ExecuteScript = 1
				EXECUTE sp_executesql @sql
			--ELSE
			--	SELECT @sql
		END
	ELSE 
	IF @DropLogin = 1 
	PRINT 'Login ''' +@UserName+''' Does Not Exist'
	
END
GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.restorePerms') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.restorePerms AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[restorePerms](
    @DBname NVARCHAR(128),
	@SnapshotID INT = NULL
 )
AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to remove all users from a database,
            then Users will be added back using the latest permissions snapshot for that database.

	Parameters:
			@DBName - REQUIRED - Name of the Database you want to drop users and apply a Permissions Snapshot to.
			@SnapshotID - OPTIONAL - ID of the specific Snapshot you want to apply.

	Usage:	
			--Drop all users from MyDB and restore the latest Permissions Snapshot for that database;
			EXEC [perms].[restorePerms] @DBName='MyDB';
			
***************************************************************************/

BEGIN
	SET NOCOUNT ON;

	--Drop all Users from the database
	EXEC perms.removeAllUsersFromDB @DBname

	--Restore Users from latest snapshot
	EXEC perms.applyPermissions 
		@DBName = @DBname,
		@SnapshotID = @SnapshotID,
		@CreateLogins = 1,
		@ExecuteScript = 1

END

GO


--If our procedure doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.snapshotEverything') IS NULL
  EXEC sp_executesql N'CREATE PROCEDURE perms.snapshotEverything AS SELECT 1;';
GO

ALTER PROCEDURE [perms].[snapshotEverything]

AS

/**************************************************************************
	Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
			MIT License
			Copyright (c) 2017-2018 Eric Cobb
			View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
			
	Purpose: 
			This stored procedure is used to create a snapshot of all of the current Server & Database level permissions on this SQL Server

	Parameters:
			NONE

	Usage:	
			--Take a Permissions Snapshot of this SQL Sever
			EXEC [perms].[snapshotEverything];
***************************************************************************/

BEGIN
	
	SET NOCOUNT ON;
	--snapshot the server permissions
	EXEC [perms].[createSeverSnapshot];
	--snapshot all of the database permissions
    EXEC [perms].[snapshotAllDatabases];

END
GO


/**************************************************************************
	Create Views
***************************************************************************/

--If our view doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.vwPerms_listCurrentSnapshots') IS NULL
  EXEC sp_executesql N'CREATE VIEW [perms].[vwPerms_listCurrentSnapshots] AS SELECT [DB] = DB_NAME();';
GO

ALTER VIEW [perms].[vwPerms_listCurrentSnapshots] 

AS
	/**************************************************************************
		Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
				MIT License
				Copyright (c) 2017 Eric Cobb
				View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
		Purpose: 
				This view returns a list of the most recent Permissions Snapshots for each database
					
	***************************************************************************/

SELECT ID, [DatabaseName], [CaptureDate]
FROM(SELECT ID, [DatabaseName], [CaptureDate] 
		,ROW_NUMBER() OVER (PARTITION BY [DatabaseName] ORDER BY [CaptureDate] DESC) AS rn
	FROM perms.snapshots
) s
WHERE s.rn = 1

GO


--If our view doesn't already exist, create one with a dummy query to be overwritten.
IF OBJECT_ID('perms.vwPerms_listCurrentDBPermissions') IS NULL
  EXEC sp_executesql N'CREATE VIEW [perms].[vwPerms_listCurrentDBPermissions] AS SELECT [DB] = DB_NAME();';
GO


ALTER VIEW [perms].[vwPerms_listCurrentDBPermissions]
AS
	/**************************************************************************
		Author: Eric Cobb - http://www.sqlnuggets.com/
		License:
				MIT License
				Copyright (c) 2017 Eric Cobb
				View full license disclosure: https:/ericcobb/SQL-Server-Permissions-Manager/blob/master/LICENSE
		Purpose: 
				This view returns a list of the most recent Permissions Snapshots for each database, displaying users and their assigned permissions.					
	***************************************************************************/

	SELECT TOP 100 PERCENT [SnapshotID] = ID, [CaptureDate], [DatabaseName]
			,rm.[username]
			,rm.[PermType]
			,rm.[Perm]
	FROM(SELECT ID, [DatabaseName], [CaptureDate] 
			,ROW_NUMBER() OVER (PARTITION BY [DatabaseName] ORDER BY [CaptureDate] DESC) AS rn
		FROM perms.snapshots
		) s
		INNER JOIN (SELECT [PermType] = 'User-Login Mapping'
							,[UserName] 
							,[Perm] = ' FROM LOGIN ' + [loginname]
							,[SnapshotID]
					 FROM [perms].[Users] u
					UNION
					SELECT [PermType] = CASE WHEN r.[roletype] = 'R' THEN 'Database Role' ELSE ' Application Role' END
							,[UserName] = [rolename]
							,[Perm] = NULL
							,[SnapshotID]
					FROM [perms].[Roles] r
					UNION 
					SELECT [PermType] = 'Role Memberships'
							,[UserName]
							,[Perm] = [rolename]
							,[SnapshotID]
					FROM [perms].[RoleMemberships] rm
					UNION
					SELECT [PermType] = 'Object Permission'
							,[UserName]
							,[Perm] = CASE WHEN state <> 'W' THEN [StateDesc] + SPACE(1) ELSE 'GRANT ' END
										+ [PermissionName] 
										+ ' ON [' +  [schemaname] + '].[' + [objectname] + '] TO [' + [username] + ']'
										+ CASE WHEN [state] <> 'W' THEN SPACE(0) ELSE ' (WITH GRANT OPTION)' END
							,[SnapshotID]
					FROM [perms].[ObjectPermissions] op
					UNION 
					SELECT [PermType] = 'Schema Permission'
							,[UserName]
							,[Perm] = CASE WHEN [state] <> 'W' THEN [StateDesc] + SPACE(1) ELSE 'GRANT ' END
										+ [PermissionName] 
										+ ' ON [' + [schemaname] + '] TO [' + [username] + ']'
										+ CASE WHEN [state] <> 'W' THEN SPACE(0) ELSE ' (WITH GRANT OPTION)' END
							,[SnapshotID]
					FROM [perms].[SchemaPermissions] 
					UNION
					SELECT [PermType] = 'Database Permission'
							,[UserName]
							,[Perm] = StateDesc + ' ' + PermissionName
							,[SnapshotID]
					FROM perms.DatabasePermissions
					) rm ON rm.[SnapshotID] = s.ID
	WHERE s.rn = 1
	ORDER BY [DatabaseName],[username],[PermType],[Perm];


GO