MapLoginToDbRole.ps1

<#
.SYNOPSIS
    Applies SQL group membership (unless exists) on availablitiy group nodes using windows login, from a definition file.
.DESCRIPTION
    Applies SQL group membership (unless exists) on availablitiy group nodes using windows logins and groups, from a definition file.
    Based on a role definition file, SQL snippets are generated and then executed.
    Removes any login/dbuser mapping that are in conflict with the securityDefinitionFile specification.
.PARAMETER masterEndpoints
   List of FQDN[,<port>] of SQL nodes participating in the availability group
.PARAMETER instanceEndpoint
   FQDN[,<port>] of SQL listener.
.PARAMETER securityDefinitionFile
  Filename of a json file containing an array of login or group mapped to a database user name and a list of roles. 
  Login can be equal to db username (without domain).
  Example contents of this file:
 [
  {
    "adLogin": "DOMAIN\\UserAccount",
    "dbUser": "AppUserAccount",
    "dbRoles": [ "db_reporting", "db_datawriter" ]
  },

  {
    "adLogin": "DOMAIN\\Group",
    "dbUser": "Log-Administrators",
    "dbRoles": [ "db_datareader", "db_logreader" ]
  }
]
.PARAMETER Database
 Name of the target database
 .PARAMETER Username
 If provided, overrides default authentication (Windows Authentication) and uses SQL authentication using the specified Username.
 .PARAMETER Password
 Password to use wuth SQL authentication, used if Username parameter is specified
#>
param(
	[string[]] $masterEndpoints = @("."),
	[string] $instanceEndpoint = ".",
	[string] $securityDefinitionFile = "Permissions.json",
	[string] $Database = "",
	[string] $Username, #Username of a db adminstrator for target, is optional.
	[string] $Password
  [bool] $whatIf = $false,
);

$securityDefinitionObject = ConvertFrom-Json ([system.io.file]::ReadAllText((Resolve-Path $securityDefinitionFile).ProviderPath))

# Basic validation of entries
$securityDefinitionObject | % {
	$Entry = $_
	if ([string]::IsNullOrEmpty($Entry.adLogin) -or [string]::IsNullOrEmpty($Entry.dbUser) -or ($Entry.dbRoles -eq $null) -or ($Entry.dbRoles.count -eq 0)) {
		throw "$securityDefinitionFile contains an invalid object: Must have non-empty properties [string]adlogin, [string]dbUser and [string[]]dbRoles: $Entry "
	}
}


# Run on $masterEndpoints

# 0 = Windows AD login or AD group
# 1 = Database name

$CreateLoginTemplate = @"
	IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = '{0}')
	BEGIN
		CREATE LOGIN [{0}] FROM WINDOWS WITH DEFAULT_DATABASE=[{1}]
	END
"@


# Run on $instanceEndpoint
# 0 = DB Instance User Name
# 1 = Windows AD login or AD group

$CreateApplicativeUserTemplate = @"
	IF NOT EXISTS(SELECT top 1 1 FROM sys.database_principals WHERE name = '{0}')
	BEGIN
		IF EXISTS (
			SELECT top 1 1 FROM sys.database_principals dp
			inner join sys.server_principals sp on sp.sid = dp.sid
			WHERE sp.name = '{1}'
		)
		BEGIN
			DROP USER [{1}]
		END

		CREATE USER [{0}] FOR LOGIN [{1}] WITH DEFAULT_SCHEMA=[dbo]
	END
"@


# Run on $instanceEndpoint
# 0 = DB Role
# 1 = DB Instance User Name

$adduserToRoleTemplate = @"
	ALTER ROLE [{0}] ADD MEMBER [{1}]
"@


function SQLQuery {
	param ($ServerInstance, $query, $debugEntry);
	if ($whatIf -eq $true) {
		Write-Verbose "Execute the following script on '[$ServerInstance].[$Database]':"
		Write-Verbose "-- BEGIN SCRIPT"
		Write-Verbose $query
		Write-Verbose "-- END SCRIPT"
	}
	else {
		Write-Verbose $CreateLoginForUser
		try
		{
			if ($Username) {
				Invoke-Sqlcmd -Query $query -Username $Username -Password $Password `
					-ServerInstance $ServerInstance -Database $Database -errorAction stop
			}else {
				Invoke-Sqlcmd -Query $query `
					-ServerInstance $ServerInstance -Database $Database -errorAction stop
			}
		} catch {
			$debugEntryString = ConvertTo-Json $debugEntry
			throw "Sql error: $($_.Exception.Message) - Query: $query , Entry: $debugEntryString"
		}
	}
}

# Creates logins on AG Nodes
$masterEndpoints | % {
	$endPoint = $_
	$securityDefinitionObject | % {
		$Entry = $_
		$CreateLoginForUser  = $CreateLoginTemplate -f $entry.adLogin, $Database
		SQLQuery -ServerInstance $endPoint -Query $CreateLoginForUser -Database "master" -debugEntry $Entry
	}
}

# Creates user/logins
# and maps user to role(s)
$securityDefinitionObject | % {
	$Entry = $_
	$CreateDbUserForLogin  = $CreateApplicativeUserTemplate -f $Entry.dbUser, $Entry.adLogin
	SQLQuery -ServerInstance $instanceEndpoint -Query $CreateDbUserForLogin -Database $Database -debugEntry $Entry

	$Entry.dbRoles | % {
		$AddRoleForDbUser= $adduserToRoleTemplate -f $_, $Entry.dbUser
		SQLQuery -ServerInstance $instanceEndpoint -Query $AddRoleForDbUser -Database $Database -debugEntry $Entry
	}
}