SQL SERVER: Generate Grants for a Database, and role memberships for a database. Can be run before a backup to store security and then applied post backup.

Generate Security.bat

@rem bat file to ease use of the split script

@%~d0
@cd "%~dp0"

powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "%~dp0Generate Security.ps1" -OutPutFile "%~dp0Security.sql"

@echo Done.
@pause

Generate Security.ps1

[CmdletBinding()]
Param (
   [Parameter(Mandatory=$true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
   [string] $DataSource, 
   [Parameter(Mandatory=$true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
   [string[]] $Databases,
   [Parameter(Mandatory=$false)]
   [System.IO.FileInfo]$OutPutFile = $null

)
begin {
    function WriteOutPut() {
        Param (
            [Parameter(ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)]
            [string]$msg = $null,
            [switch]$isError = $false
        )
        if ($msg) {
            if ($OutPutFile) {
                $sb.AppendLine($msg) | Out-Null
            } 
            if (!$isError) {
                Write-Host $msg
            } else {
                Write-Error $msg
            }
        }
    }
    function EnumObjectsPermissions($objects, $type_prefix = $null, [switch] $include_schema) {
        foreach($obj in $objects | Where-Object { $_.Schema -ine "cdc" } | Sort-Object { $_.Name } ) {
            foreach($permission in $obj.EnumObjectPermissions() | Where-Object { $_.Grantee -ine 'dbo' } | Sort-Object -Property Grantee, PermissionType) {
                if($include_schema) {
                    $name = "$type_prefix[$($permission.ObjectSchema)].[$($obj.Name)]"
                } else {
                    $name = "$type_prefix[$($obj.Name)]"
                }
                WriteOutPut "$($permission.PermissionState.ToString().ToUpper()) $($permission.PermissionType.ToString().ToUpper()) ON $name TO [$($permission.Grantee)];"
            }
        }
    }
    function EnumDBPermissions($objects) {
        foreach($obj in $objects | Sort-Object { $_.Name } ) {
            foreach($permission in $db.EnumDatabasePermissions($obj.Name) | Where-Object { $_.Grantee -ine 'dbo' } | Sort-Object -Property Grantee, PermissionType) {
                WriteOutPut "$($permission.PermissionState.ToString().ToUpper()) $($permission.PermissionType.ToString().ToUpper()) TO [$($permission.Grantee)];"
            }
        }
    }
    [System.Text.StringBuilder] $sb = [System.Text.StringBuilder]::new() 
    if ($OutPutFile.Exists) {
        $OutPutFile.Delete() | Out-Null
    }
    
    $sep = "*" * 60  
    $scriptRoot = [System.IO.Directory]::GetParent($MyInvocation.MyCommand.Definition)
    Clear-Host
    WriteOutPut "PRINT 'Generated for $($env:USERNAME) on $(get-date -Format "MM-dd-yyyy HH:mm")'"

    # Load SMO assemblys
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | Out-Null

    $server = New-Object Microsoft.SqlServer.Management.SMO.Server($DataSource)
    $server.ConnectionContext.Connect();
    
    WriteOutPut "--DROP ROLE MEMBERS PROC:"
    $proc = [System.IO.File]::ReadAllText("$($scriptRoot.FullName)\zDropRoleMembers.sql")
    WriteOutPut $proc
    WriteOutPut "`r`n`r`n"
}
process {
    foreach ($Database in $Databases) {
        WriteOutPut "GO"
        WriteOutPut "--$sep" 
        WriteOutPut "PRINT 'SERVER: $DataSource DATABASE: $Database'"
        WriteOutPut "--$sep`r`n" 

        try { 
            $db = $server.Databases[$Database]

            if (!$db) {
                WriteOutPut "--$Database could not be found on the server, or you do not have access." -isError
                return;
            }
            WriteOutPut "USE [$Database]"
            WriteOutPut "DECLARE @Db_Name sysname = DB_NAME()"
            WriteOutPut "`r`n"

            WriteOutPut "--USERS:" 
            foreach ($obj in $db.Users | Where-Object { !$_.IsSystemObject -and $_.Login } | Sort-Object { $_.Name }) { 
                WriteOutPut "PRINT 'USER [$($obj.Name)]'" 
                WriteOutPut "IF DATABASE_PRINCIPAL_ID('$($obj.Name)') IS NULL BEGIN"
                WriteOutPut "`tCREATE USER [$($obj.Name)] FOR LOGIN [$($obj.Login)]"
                WriteOutPut "END "
                if ($obj.LoginType -ieq "SqlLogin") {
                    # https://blogs.lessthandot.com/index.php/datamgmt/datadesign/do-you-still-use-sp_change_users_login/
                    WriteOutPut "ALTER USER [$($obj.Name)] WITH LOGIN = [$($obj.Login)] --autofix" 
                }
                foreach ($permission in $db.EnumDatabasePermissions($obj.Name) | Where-Object { $_.Grantee -ine 'dbo' } | Sort-Object -Property Grantee, PermissionType) {
                    WriteOutPut "$($permission.PermissionState.ToString().ToUpper()) $($permission.PermissionType) TO [$($permission.Grantee)];"
                }
                foreach ($permission in $db.EnumObjectPermissions($obj.Name) | Where-Object { $_.Grantee -ine 'dbo' } | Sort-Object -Property Grantee, PermissionType) {
                    WriteOutPut "$($permission.PermissionState.ToString().ToUpper()) $($permission.PermissionType) ON [$($permission.ObjectName)] TO [$($permission.Grantee)];"
                }
                WriteOutPut "`r`n"
            }
            $roles = $db.Roles 
            WriteOutPut "--ROLE MEMBERSHIPS:" 
            foreach ($role in $roles | Where-Object { $_.Name -ine "public" }) {
                WriteOutPut "PRINT 'ROLE MEMBERSHIPS [$($role.Name)]'" 
                if (!$role.IsFixedRole) {
                    WriteOutPut "IF DATABASE_PRINCIPAL_ID('$($role.Name)') IS NULL BEGIN"
                    WriteOutPut "`tCREATE ROLE [$($role.Name)]"
                    WriteOutPut "END"
                }
                WriteOutPut "EXEC #DropRoleMembers @DBName = @Db_Name, @RoleName = '$($role.Name)'"
                foreach( $member in $role.EnumMembers() | Where-Object { $_ -ine "dbo" }) {
                    WriteOutPut "ALTER ROLE [$($role.Name)] ADD MEMBER [$member];"
                }
                WriteOutPut "`r`n"
            }
            WriteOutPut "--ROLES:" 
            EnumDBPermissions -objects $roles
            WriteOutPut "--SCHEMAS:" 
            EnumObjectsPermissions -objects $db.Schemas -type_prefix "SCHEMA::"
            WriteOutPut "--TABLES:" 
            EnumObjectsPermissions -objects $db.Tables -include_schema
            WriteOutPut "--VIEWS:" 
            EnumObjectsPermissions -objects $db.Views -include_schema
            WriteOutPut "--FUNCTIONS:" 
            EnumObjectsPermissions -objects $db.UserDefinedFunctions -include_schema
            WriteOutPut "--PROCEDURES:" 
            EnumObjectsPermissions -objects $db.StoredProcedures -include_schema
            WriteOutPut "--EXT PROCEDURES:" 
            EnumObjectsPermissions -objects $db.ExtendedStoredProcedures -include_schema
            WriteOutPut "--TABLE TYPES:" 
            EnumObjectsPermissions -objects $db.UserDefinedTableTypes -include_schema -type_prefix "TYPE::"
            WriteOutPut "--CERTS:" 
            EnumObjectsPermissions -objects $db.Certificates -include_schema
            WriteOutPut "--SEQUENCES:" 
            EnumObjectsPermissions -objects $db.Sequences -include_schema
            WriteOutPut "--SYMMETRIC KEYS:" 
            EnumObjectsPermissions -objects $db.SymmetricKeys  -type_prefix "SYMMETRIC KEY::"
            WriteOutPut "--ASYMMETRIC KEYS:" 
            EnumObjectsPermissions -objects $db.AsymmetricKeys -type_prefix "ASYMMETRIC KEY::"
            WriteOutPut "--SYNONYMS:" 
            EnumObjectsPermissions -objects $db.Synonyms -include_schema
            WriteOutPut "--USER DEFINED AGGREGATES:" 
            EnumObjectsPermissions -objects $db.UserDefinedAggregates -include_schema

            WriteOutPut "`r`n"
        } catch {
            WriteOutPut "--$($_.Exception.Message)" -isError 
        }

    }
}
end {
    if ($OutPutFile) {
        [System.IO.File]::WriteAllText($OutPutFile.FullName, ($sb.ToString()))
    }
    if($server) { 
        $server.ConnectionContext.Disconnect()
    }
}

zDropRoleMembers.sql

IF OBJECT_ID (N'tempdb..#DropRoleMembers') IS NULL BEGIN
	EXEC('CREATE PROCEDURE #DropRoleMembers AS SET NOCOUNT ON;'); 
END
GO 

ALTER PROCEDURE #DropRoleMembers(@DBName sysname, @RoleName sysname)
AS 
BEGIN
	SET NOCOUNT ON
	DECLARE @cmd AS NVARCHAR(MAX) = N'USE [' + @DBName + ']' + CHAR(10);
	DECLARE @cmds TABLE (
		cmd VARCHAR(8000)
	)

	SELECT @cmd = @cmd + N'
		SELECT ''PRINT ''''--ALTER ROLE ' + QUOTENAME(@rolename) + ' DROP MEMBER '' + QUOTENAME(members.[name]) + '';'''''' + CHAR(10)
			+ ''ALTER ROLE ' + QUOTENAME(@rolename) + ' DROP MEMBER '' + QUOTENAME(members.[name]) + '';''
	FROM [' + @DBName + '].sys.database_role_members AS rolemembers
		JOIN [' + @DBName + '].sys.database_principals AS roles 
			ON roles.[principal_id] = rolemembers.[role_principal_id]
		JOIN [' + @DBName + '].sys.database_principals AS members 
			ON members.[principal_id] = rolemembers.[member_principal_id]
	WHERE roles.[name]= ''' + @RoleName + ''''

	-- generate the drop commands first
	INSERT INTO @cmds EXEC(@cmd);
	
	SELECT @cmd = N'USE [' + @DBName + ']' + CHAR(10) + STUFF((
		SELECT CHAR(10) + cmd FROM @cmds FOR XML PATH('')), 1, 1, '')
	
	--PRINT @cmd
	EXEC(@cmd);
END
GO

Can be run using the bat file or like so:

Invoke-Sqlcmd -ServerInstance "SERVERNAME" -Database "master" -Query "select name from sys.databases WHERE database_id > 4" | select-object -expand Name | & '.\Generate Security.ps1' -DataSource SERVERNAME

OR:

& '.\Generate Security.ps1' -DataSource SERVERNAME -Databases "Foo", "Bar"

OR

"Foo", "Bar" | & '.\Generate Security.ps1' -DataSource SERVERNAME