Re-apply Database Permissions

# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$PermissionsFolder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database msdb -Query "DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @DBPermissions VARCHAR(300);
DECLARE @DirTree TABLE
       (
       subdirectory NVARCHAR(255),
       depth        INT
       );
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @DBPermissions = @DefaultBackupDirectory+'\DBPermissions';

INSERT INTO @DirTree
    (subdirectory
       , depth
    )
EXEC master.sys.xp_dirtree @DefaultBackupDirectory;
IF NOT EXISTS
     (
      SELECT 1
      FROM @DirTree
      WHERE subdirectory = 'DBPermissions'
    AND depth = 1
     )
    EXEC master.dbo.xp_create_subdir @DBPermissions;
 SELECT @DBPermissions DBPermissions;"


 

$databases = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "select name  FROM master.sys.databases sd
            WHERE sd.name in ('perfstat','arg_afw_pdb' )
                  AND sd.[user_access_desc] = 'MULTI_USER'
                  AND sd.[is_in_standby] = 0;"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
#$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.name #databasename from the select query
$dbname
#connect to each instance\database and generate security script and output to files
$DBname
#invoke-sqlcmd -ServerInstance EDIDVWPSTPOCDB1\SQL141 -Database DBATasks -Query "exec DBATasks..[sp_DatabaseRestore] @Database = $DBname, @BackupPathFull = '\\EDIDVWPSTPOCDB1\d$\Program Files\Microsoft SQL Server\MSSQL12.SQL141\MSSQL\Backup\EDIDVWPSTPOCDB1\'"
$DBPermissionScript = $PermissionsFolder.DBPermissions +"\" + $DBname +"_DBPermissions.sql"
$DBPermissionScript
invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database master -InputFIle $DBPermissionScript
}