Refactoring PowerShell – Switch Statements

Regardless of your experience within technology, the process of creating code usually starts out with a solution that is just enough to get the job done. The solution is then typically tweaked and improved continuously until it is either “production worthy” or “good enough for the requirements“. This process of improvement is called refactoring. Refactoring is a skill that all technical professionals should become proficient in, regardless if you are an IT Pro, a developer or just someone who needs to automate things.

There are many reasons to refactor code, including:

  • Add new features
  • Remove unused features
  • Improve performance
  • Increase readability, maintainability or test-ability
  • Improve design
  • Improve adherence to standards or best practices

Refactoring in Code Reviews

One of the jobs of a code reviewer is to suggest areas that could be refactored to improve some of the areas above. I’ve often found that I’ll suggest the same set of refactorings in my reviews. So rather than just putting the suggesting into the code review, I thought I’d start writing them down here in a series that I could refer contributors to as well as help anyone else who happens to come across these.

Unit Tests and Refactoring

Because refactoring requires changing code, how can we be sure that we’re not breaking functionality or introducing bugs? That is where unit testing comes in. With PowerShell, we’ll typically use the PowerShell Pester module to create unit tests that allow us to more safely refactor code. Unit testing is beyond the scope of this post.

Switch Statement Refactoring

One of the common patterns I’ve come across is PowerShell switch statements being used is to map from one values to another set of values. For example:

$colourName = 'green'
$colourValue = switch ($colourName) {
'red' { 0xFF0000; break }
'green' { 0x00FF00; break }
'blue' { 0x0000FF; break }
'white' { 0xFFFFFF; break }
default { 0x0 }
return $colourValue

This converts a colour name (e.g. red, green, blue, white) to a colour value. If a colour name can not be matched then it returns 0x0 (black). Admittedly, this is a bit of an unlikely example, but it demonstrates the pattern.

This is by no means incorrect or “bad code”. It is completely valid and solves the problem perfectly well. But as you can see this requires a lot of code to perform a simple mapping.

Mapping using a Hash Table

An alternative to using a switch statement is to use a hash table:

$colourName = 'green'
$colourMap = @{
red = 0xFF0000
green = 0x00FF00
blue = 0x0000FF
white = 0xFFFFFF
$colourValue = $colourMap[$colourName]
return $colourValue

This can simplify the code slightly by removing the break statement and braces.

Note: The break statement is not strictly required in this example from a functional perspective, but including them increases overall performance of the switch statement.

You may have noticed that the hash table above does not quite match the behavior of the switch statement: the default mapping to 0x0 is not handled. So, in this case, we’d need to include additional code to handle this:

$colourName = 'green'
$colourMap = @{
red = 0xFF0000
green = 0x00FF00
blue = 0x0000FF
white = 0xFFFFFF
$colourValue = ($colourMap[$colourName], 0x0, 1 -ne $null)[0] # Null Coalescing
return $colourValue

To handle the default we’re using a quasi null coalescing operator. PowerShell doesn’t have a native null coalescing operator like many languages, but it does have a way of simulating it using the line:

$notNull = ($x, $y, 1 -ne $null)[0] # Null Coalescing in PowerShell

You could definitely argue that using a hash table mapping with a null coalescing operator does not make the code easier to read or maintain. But the purpose here is not to define which approach is best, rather to offer alternative patterns.

One other benefit of using a hash table for mapping is that it can be separated out into a separate psd1 file. This allows editing of the mapping table elements without having to edit the code itself:

$colourName = 'green'
$colourMap = Import-LocalizedData -FileName mapping.psd1
$colourValue = ($colourMap[$colourName], 0x0, 1 -ne $null)[0] # Null Coalescing
return $colourValue

The psd1 file containing the mapping data (mapping.psd1):

red = 0xFF0000
green = 0x00FF00
blue = 0x0000FF
white = 0xFFFFFF
view raw Mapping.psd1 hosted with ❤ by GitHub

Reversing the Mapping

How do we use a similar pattern to reverse the mapping? For example, mapping a colour value back to a colour name:

$colourValue = 0x00FF00
$colourName = switch ($colourValue) {
0xFF0000 { 'red'; break }
0x00FF00 { 'green' ; break }
0x0000FF { 'blue'; break }
0xFFFFFF { 'white'; break }
default { 'none' }
return $colourName

To implement the same functionality using a hash table also including the null coalescing operator you could use:

$colourValue = 0x00FF00
$colourMap = @{
0xFF0000 = 'red'
0x00FF00 = 'green'
0x0000FF = 'blue'
0xFFFFFF = 'white'
$colourName = ($colourMap[$colourValue], 0x0, 1 -ne $null)[0] # Null Coalescing
return $colourName

Using a Hash Table with Script Values

Switch blocks may contain more than just a single statement. For example:

$VerbosePreference = 'Continue'
$action = 'New'
$path = 'c:\somefile.txt'
$result = switch ($action) {
'New' {
Write-Verbose -Message 'Execute New-Item'
New-Item -Path $path
'Remove' {
Write-Verbose -Message 'Execute Remove-Item'
Remove-Item -Path $path
'Get' {
Write-Verbose -Message 'Execute Get-Item'
Get-Item -Path $path
Default {
Write-Verbose -Message 'Invalid Action'
return $result

If your switch blocks do more than just perform a mapping, you can assign script blocks to the hash table values instead:

$VerbosePreference = 'Continue'
$action = 'New'
$path = 'c:\somefile.txt'
$actions = @{
'New' = {
Write-Verbose -Message 'Execute New-Item'
New-Item -Path $path
'Remove' = {
Write-Verbose -Message 'Execute Remove-Item'
Remove-Item -Path $path
'Get' = {
Write-Verbose -Message 'Execute Get-Item'
Get-Item -Path $path
return ($actions[$action], {Write-Verbose -Message 'Invalid Action'}, 1 -ne $null)[0].Invoke()

Instead of containing a value in each hash table item, a script block is specified. The Invoke() method can then be called on the script block.

Enumerated Types

If you’re using PowerShell 5.0 or above (you hopefully are), you’re also able to use the enum keyword to define an enumerated type that can also be used to replace switch statements in some situations.

$colourName = 'green'
# Only needs to be declared once
enum colour {
red = 0xFF0000
green = 0x00FF00
blue = 0x0000FF
white = 0xFFFFFF
return ([colour] $colourName).value__

The enumerated type only needs to be declared once.

But what do we need to do if we want to have a default returned if the value is invalid in the mapping? In that case we need to use the TryParse method of the enumerated type to try and parse the value and return a default value if it is invalid:

$colourName = 'grey'
# Only needs to be declared once
enum colour {
red = 0xFF0000
green = 0x00FF00
blue = 0x0000FF
white = 0xFFFFFF
$colourValue = [colour]::white
if (-not [colour]::TryParse($colourName, $true, [ref] $colourValue)) {
return 0x0
} else {
return $colourValue.value__

However, we can’t assign scriptblocks to the values in an enumerated type – only constant values may be assigned. This means we can’t implement scenarios where we’d like to have the value contain more than one instruction. But this shouldn’t be too much of a problem, because if you do find yourself being limited by this, then you should probably be looking to use more advanced object oriented programming patterns such as polymorphism – which is well beyond the scope of this post. But if you’re interested to know more, review this article (not PowerShell specific).

Wrapping Up

This post is all about looking at different ways of writing the same code. It isn’t trying to say which way is better or worse. If you have a preference and it works for you, by all means, keep on using it. This is simply to provide alternative methods that may or may not make code more readable and maintainable.

Feel free to comment with alternative methods of refactoring switch statements.





Allow Integer Parameter to Accept Null in a PowerShell Function

One of the great things about PowerShell being based on .NET is that we get to the huge number of types built into the framework.

A problem I came across today was that I needed to have a function that took a mandatory integer parameter, but that parameter needed to allow Null. In .NET there is a generic type System.Nullable<T> that allows other types to take on a null value.

function Set-AdapterVlan
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
if ($null -eq $VlanId)
$Adapter | Set-VMNetworkAdapterVlan -Untagged
$Adapter | Set-VMNetworkAdapterVlan -VlanId $VlanId -Access

This allows me to call the function above with the following:

Set-AdapterVlan -Adapter $adapter -Vlan $null

Which will clear the Vlan ID from the virtual network adapter.

The magic is in the parameter definition:

[Parameter(Mandatory = $true)]

The [AllowNull()] attribute allows the $VlanId parameter to accept a Null even though it is mandatory, and the [Nullable[System.Int32]] allows $VlanId to be assigned a null value.

This isn’t something I use often, but thought it was worth sharing.

Enable CORS Support in Cosmos DB using PowerShell

Support for Cross-Origin Resource Sharing (CORS) was recently added to Cosmos DB. If you want to enable CORS on an existing Cosmos DB account or create a new Cosmos DB account with CORS enabled it is very easy to do with Azure Resource Manager (ARM) templates or the Azure Portal.

But what if you’re wanting to find out the state of the CORS setting on an account or set it using PowerShell? Well, look no further.

The Cosmos DB PowerShell module (version 3.0.0 and above) supports creating Cosmos DB accounts with CORS enabled as well as updating and removing the CORS headers setting on an existing account. You can also retrieve the CORS setting for an existing Cosmos DB account.

Installing the CosmosDB Module

The first thing you need to do is install the CosmosDB PowerShell module from the PowerShell Gallery by running this in a PowerShell console:

Install-Module -Name CosmosDB -MinimumVersion


This will also install the Az PowerShell modules Az.Accounts and Az.Resources modules if they are not installed on your machine. The *-CosmosDbAccount functions in the CosmosDB module are dependent on these modules.

Note: The CosmosDB PowerShell module and the Az PowerShell modules are completely cross-platform and support Linux, MacOS and Windows. Running in either Windows PowerShell (Windows) or PowerShell Core (cross-platform) is supported.

Versions of the CosmosDB PowerShell module earlier than use the older AzureRm/AzureRm.NetCore modules and do not support the CORS setting.

Authenticating to Azure with ‘Az’

Before using the CosmosDB PowerShell module accounts functions to work with CORS settings you’ll first need to authenticate to Azure using the Az PowerShell Modules. If you’re planning on automating this process you’ll want to authenticate to Azure using a Service Principal identity.

Side note: if you’re using this module in an Azure DevOps build/release pipeline the Azure PowerShell task will take care of the Service Principal authentication process for you:


But if you’re just doing a little bit of experimentation then you can just use an interactive authentication process.

To use the interactive authentication process just enter into your PowerShell console:


then follow the instructions.


Create a Cosmos DB Account with CORS enabled

Once you have authenticated to Azure, you can use the New-CosmosDbAccount function to create a new account:

New-CosmosDbAccount `
-Name 'dsrcosmosdbtest' `
-ResourceGroupName 'dsrcosmosdbtest-rgp' `
-Location 'westus' `
-AllowedOrigin '',''

ss_cosmosdbcors_newcosmosdbaccountThis will create a new Cosmos DB account with the name dsrcosmosdbtest in the resource group dsrcosmosdbtest-rgp in the West US location and with CORS allowed origins of and

Important: the New-CosmosDbAccount command assumes the resource group that is specified in the ResourceGroup parameter already exists and you have contributor access to it. If the resource group doesn’t exist then you can create it using the New-AzResourceGroup function or some other method.

It will take Azure a few minutes to create the new Cosmos DB account for you.

Side note: But if you want your PowerShell automation or script to be able to get on and do other tasks in the meantime, then add the -AsJob parameter to the New-CosmosDbAccountcall. This will cause the function to immediately return and provide you a Job object that you can use to periodically query the state of the Job. More information on using PowerShell Jobs can be found here.

Be aware, you won’t be able to use the Cosmos DB account until the Job is completed.

If you look in the Azure Portal, you will find the new Cosmos DB account with the CORS allowed origin values set as per your command:


Get the CORS Allowed Origins on a Cosmos DB Account

Getting the current CORS Allowed Origins value on an account is easy too. Just run the following PowerShell command:

(Get-CosmosDbAccount `
-Name 'dsrcosmosdbtest' `
-ResourceGroupName 'dsrcosmosdbtest-rgp').Properties.Cors.AllowedOrigins


This will return a string containing all the CORS Allowed Origins for the Cosmos DB account dsrcosmosdbtest.

You could easily split this string into an array variable by using:

$corsAllowedOrigins = (Get-CosmosDbAccount `
-Name 'dsrcosmosdbtest' `
-ResourceGroupName 'dsrcosmosdbtest-rgp').Properties.Cors.AllowedOrigins -split ','


Update the CORS Allowed Origins on an existing Cosmos DB Account

To set the CORS Allowed Origins on an existing account use the Set-CosmosDbAccount function:

Set-CosmosDbAccount `
-Name 'dsrcosmosdbtest' `
-ResourceGroupName 'dsrcosmosdbtest-rgp' `
-AllowedOrigin ''


This will take a few minutes to update. So you can use the -AsJob parameter to run this as a Job.

Remove the CORS Allowed Origins from an existing Cosmos DB Account

You can remove the CORS Allowed Origins setting by setting using the Set-CosmosDbAccount function but passing in an empty string to the AllowedOrigin parameter:

Set-CosmosDbAccount `
-Name 'dsrcosmosdbtest' `
-ResourceGroupName 'dsrcosmosdbtest-rgp' `
-AllowedOrigin ''


This will take a few minutes to update as well. As always, you can use the -AsJob parameter to run this as a Job.


Final Words

Hopefully, you can see it is fairly simple to automate and work with the Cosmos DB CORS Allowed Origins setting using the PowerShell Cosmos DB module.

If you have any issues or queries or would like to contribute to the PowerShell Cosmos DB module, please head over to the GitHub repository.


Use Pester to Test Azure Resource Manager Templates for Best Practices

Recently I came across the amazing Secure DevOps Kit for Azure (AzSK). This contains a really useful AzSK PowerShell Module that contains cmdlets for performing different types of security scanning on Azure Resources, Subscriptions and Resource Manager Templates.

The feature of this module that I was most interested in for my current project was being able to scan ARM templates for best practice violations. The module contains several

To install the module, open a PowerShell Window and run:

Install-Module -Name AzSK

Important: At the time of writing this post, the AzSK module has dependencies on the AzureRM.Profile and other AzureRM.* PowerShell modules. As of December 2018, the AzureRM.* PowerShell Modules are going to be renamed to Az.* (see this post). The AzureRM and Az modules can not be installed side-by-side, so if you’ve installed the Az PowerShell modules on your system then the installation of AzSK will fail because the AzureRM modules will also be installed and a conflict will occur.

The cmdlet we’re most interested in is the Get-AzSKARMTemplateSecurityStatus. It can be used to scan one or more ARM templates or entire folders of ARM templates for best practice violations:


This will scan the ARM templates and produce a CSV report in a folder Microsoft\AzSKLogs\ARMChecker within your $ENV:LOCALAPPDATA folder and open the folder in Explorer. This isn’t ideal for automation scenarios or using during Continuous Integration or Continuous Delivery pipelines. I’ve raised an issue with the AzSK team on GitHub to see if this can be improved.

In my case, I wanted to be able to use the PowerShell Pester Module, a PowerShell testing framework, to execute tests on the output and then use the nUnit output Pester generates to publish into a Continuous Integration pipeline. To do that I needed to create a custom test script that would take the CSV report, count the failures of each level (High, Medium or Low) and fail if any are counted in the specific level.

This is what the script looks like:

.VERSION 1.0.0
.GUID bf41177f-4d1e-481a-a126-5f0c07dd9aae
.AUTHOR Daniel Scott-Raynsford
.COPYRIGHT (c) 2018 Daniel Scott-Raynsford. All rights reserved.
.TAGS AzSK, Pester, Test
.RELEASENOTES First version.
.PRIVATEDATA 2016-Datacenter,2016-Datacenter-Server-Core
#requires -Modules @{ ModuleName="AzSK"; ModuleVersion="3.6.1" }
#requires -Modules @{ ModuleName="Pester"; ModuleVersion="4.3.0" }
Pester test for validating ARM template meets best-practices
This Pester test will validate one or more ARM templates in the specified
file path to validate that they meet the best practices.
.PARAMETER TemplatePath
The full path to the ARM template to check. This may be a path with
wild cards to check multiple files.
An array of severity values that will count as failed tests. Any violation
found in the ARM template that matches a severity in this list will cause
the Pester test to count as failed. Defaults to 'High' and 'Medium'.
.PARAMETER SkipControlsFromFile
The path to a controls file that can be use to suppress rules.
param (
[Parameter(Mandatory = $true)]
$Severity = @('High','Medium'),
Describe 'ARM template best practices' -Tag 'AzSK' {
Context 'When AzSK module is installed and run on all files in the Templates folder' {
$resultPath = Get-AzSKARMTemplateSecurityStatus `
-ARMTemplatePath $TemplatePath `
-Preview:$true `
-DoNotOpenOutputFolder `
-SkipControlsFromFile $SkipControlsFromFile `
$resultFile = (Get-ChildItem -Path $resultPath -Filter 'ARMCheckerResults_*.csv')[0].FullName
It 'Should produce a valid CSV results file ' {
$resultFile | Should -Not -BeNullOrEmpty
Test-Path -Path $resultFile | Should -Be $true
$script:resultsContent = Get-Content -Path $resultFile | ConvertFrom-Csv
$groupedResults = $script:resultsContent | Where-Object -Property Status -EQ 'Failed' | Group-Object -Property Severity
$testCases = $Severity.Foreach({@{Severity = $_}})
It 'Should have 0 failed Severity:<Severity> results' -TestCases $testCases {
param ( [System.String] $Severity )
$failedCount = $groupedResults.Where({ $_.Name -eq $Severity })[0].Count
$failedCount | Should -Be 0

You can download the script from GitHub Gist directly or get it from the PowerShell Gallery by running:

Install-Script -Name AzSKARMTemplateSecurityStatus.Test

To use it you will need to install Pester 4.3.0 and AzSK 3.6.1 modules:

Install-Module -Name Pester -MinimumVersion 4.3.0
Install-Module -Name AzSK -MinimumVersion 3.6.1

Once that is done, you can use Invoke-Pester and pass in the TemplatePath and Severity parameters to the test script:

Invoke-Pester -Script @{ Path = 'd:\Invoke-AzSKARMTemplateSecurityStatusPesterTest.ps1'; Parameters = @{ TemplatePath = 'D:\101-webapp-basic-windows\azuredeploy.json' }}

This will execute the Pester tests in the file above on the specified ARM template. The tests will fail when there are any best practice violations with the specified Severity or above. If you didn’t pass in a Severity then it will default to failing on Medium and High.


If you use the OutputFile and OutputFormat parameters to cause Pester to output an NUnit format file that most Continuous Integration tools will happily accept and use to display the output of the tests.

If you installed the script from the PowerShell Gallery, you can also run the tests like this:

AzSKARMTemplateSecurityStatus.Test -TemplatePath D:\101-webapp-basic-windows\azuredeploy.json

Finally, if you’re using Azure DevOps, you can also get this function as part Secure DevOps Kit (AzSK) CICD Extensions for Azure in the Azure DevOps Marketplace.

Which ever way you choose to consume AzSK, it is a great module and well worth including in your CI/CD pipelines to ensure your ARM templates meet best practices.



List Global Assembly Cache using PowerShell

The list of assemblies stored in the Global Assembly Cache (GAC) can be found in the registry under the HKEY_CLASSES_ROOT\Installer\Assemblies\Global key.

If you want to get a list of the assemblies registered in the GAC using PowerShell you can use this snippet:

New-PSDrive -Name HKCR -PSProvider 'Microsoft.PowerShell.Core\Registry' -Root HKEY_CLASSES_ROOT
Get-ItemProperty -Path 'HKCR:\Installer\Assemblies\Global' | Get-Member -MemberType NoteProperty


The first line registers a new drive called HKCR in PowerShell that maps to the HKEY_CLASSES_ROOT in the registry. This is required because, by default only the HKEY_CURRENT_USER and HKEY_LOCAL_MACHINE registry hives are registered as drives in PowerShell.

The second line just gets the list of registry properties in the HKEY_CLASSES_ROOT\Installer\Assemblies\Global key and displays them.



Get the ForceChangePassword Office 365 User Setting with PowerShell

Recently I was asked by a friend if I knew of a way to get the value of the setting that forces a user to change their password when the next log in to Office 365. The friend wanted to get this value for all users using PowerShell.

Changing this setting is fairly straight forward either in the Office 365 portal or using the Set-MsolUserPassword cmdlet in the MSOnline PowerShell module:


However retrieving the current value of the setting isn’t possible using Get-MoslUser cmdlet – the attribute does not appear in the returned object:


Instead, we need to use the Get-AzureADUser cmdlet in the AzureAD PowerShell Module to query the Azure Active Directory for the Office 365 tenant.

If you don’t have the AzureAD module installed, use Install-Module cmdlet to install it from the PowerShell Gallery:

Install-Module -Name AzureAD

Then connect to the AzureAD using the Connect-AzureAD cmdlet. Once connected you can run the following command to get the user object and show only the appropriate property (ForceChangePasswordNextLogin of the PasswordProfile object):


This is all fairly straight forward once you figure out which object in Azure AD contains the information required.

Managing Users & Permissions in Cosmos DB with PowerShell

If you’re just getting started with Cosmos DB, you might not have come across users and permissions in a Cosmos DB database. However, there are certain use cases where managing users and permissions are necessary. For example, if you’re wanting to be able to limit access to a particular resource (e.g. a collection, document, stored procedure) by user.

The most common usage scenario for users and permissions is if you’re implementing a Resource Token Broker type pattern, allowing client applications to directly access the Cosmos DB database.

Side note: The Cosmos DB implementation of users and permissions only provides authorization – it does not provide authentication. It would be up to your own implementation to manage the authentication. In most cases you’d use something like Azure Active Directory to provide an authentication layer.

But if you go hunting through the Azure Management Portal Cosmos DB data explorer (or Azure Storage Explorer) you won’t find any way to configure or even view users and permissions.


To manage users and permissions you need to use the Cosmos DB API directly or one of the SDKs.

But to make Cosmos DB users and permissions easier to manage from PowerShell, I created the Cosmos DB PowerShell module. This is an open source project hosted on GitHub. The Cosmos DB module allows you to manage much more than just users and permissions, but for this post I just wanted to start with these.


This module works on PowerShell 5.x and PowerShell Core 6.0.0. It probably works on PowerShell 3 and 4, but I don’t have any more machines running this version to test on.

The Cosmos DB module does not have any dependencies, except if you call the New-Cosmos DbContext function with the ResourceGroup parameter specified as this will use the AzureRM PowerShell modules to read the Master Key for the connection directly from your Cosmos DB account. So I’d recommend installing the Azure PowerShell modules or if you’re using PowerShell 6.0, install the AzureRM.NetCore modules.

Installing the Module

The best way to install the Cosmos DB PowerShell module is from the PowerShell Gallery. To install it for only your user account execute this PowerShell command:

Install-Module -Name CosmosDB -Scope CurrentUser


Or to install it for all users on the machine (requires administrator permissions):

Install-Module -Name CosmosDB


Context Variable

Update 2018-03-06

As of Cosmos DB module v2.0.1, the connection parameter has been renamed to context and the New-CosmosDbConnection function has been renamed New-CosmosDbContext. This was to be more inline with naming adopted by the Azure PowerShell project. The old connection parameters and New-CosmosDbConnection function is still available as an alias, so older scripts won’t break. But these should be changed to use the new naming if possible as I plan to deprecate the connection version at some point in the future.

This post was updated to specify the new naming, but screenshots still show the Connection aliases.

Before you get down to the process of working with Cosmos DB resources, you’ll need to create a context variable containing the information required to connect. This requires the following information:

  1. The Cosmos DB Account name
  2. The Cosmos DB Database name
  3. The Master Key for the account (you can have the Cosmos DB PowerShell module get this directly from your Azure account if you wish).

To create the connection variable we just use the New-CosmosDbContext:

$account = 'MyCosmosDBAccount'
$database = 'MyDatabase'
$key = ConvertTo-SecureString -String 'this is your master key, get it from the Azure portal' -AsPlainText -Force
$context = New-CosmosDbContext -Account $account -Database $database -Key $key


If you do not wish to specify your master key, you can have the New-CosmosDbContext function pull your master key from the Azure Management Portal directly:

$account = 'MyCosmosDBAccount'
$database = 'MyDatabase'
$resourceGroup = 'MyCosmosDBResourceGroup'
$context = New-CosmosDbContext -Account $account -Database $database -ResourceGroup $resourceGroup


Note: This requires the AzureRM.Profile and AzureRM.Resoures module on Windows PowerShell 5.x or AzureRM.Profile.NetCore and AzureRM.Resources.NetCore on PoweShell Core 6.0.0.

Managing Users

To add a user to the Cosmos DB Database use the New-CosmosDBUser function:

New-CosmosDbUser -Context $context -Id 'daniel'


To get a list of users in the database:

Get-CosmosDbUser -Context $context


To get a specific user:

Get-CosmosDbUser -Context $context -Id 'daniel'


To remove a user (this will also remove all permissions assigned to the user):

Remove-CosmosDbUser -Context $context -Id 'daniel'


Managing Permissions

Permissions in Cosmos DB are granted to a user for a specific resource. For example, you could grant a user access to just a single document, an entire collection or to a stored procedure.

To grant a permission you need to provide four pieces of information:

  1. The Id of the user to grant the permission to.
  2. An Id for the permission to create. This is just string to uniquely identify the permission.
  3. The permission mode to the permission: All or Read.
  4. The Id of the resource to grant access to. This can be generated from one of the Get-CosmosDb*ResourcePath functions in the CosmosDB PowerShell module.

In the following example, we’ll grant the user daniel all access to the TestCollection:

$userId = 'TestUserId'
$resourcePath = Get-CosmosDbCollectionResourcePath -Database 'TestDatabase' -Id 'TestCollection'
New-CosmosDbPermission -Context $context -Id 'AccessTestCollection' -UserId $userId -PermissionMode All -Resource $resourcePath


Once a permission has been granted, you can use the Get-CosmosDbPermission function to retrieve the permission and with it the Resource Token that can be used to access the resource for a limited amount of time (between 10 minutes and 5 hours).

Note: as you have the Master Key already, using the Resource Token isn’t required.

For example, to retrieve all permissions for the user with Id daniel and a resource token expiration of 600 seconds:

Get-CosmosDbPermission -Context $context -UserId 'daniel' -TokenExpiry '600' |
fl *


You can as expected delete a permission by using the Remove-CosmosDbPermission function:

Remove-CosmosDbPermission -Context $context -UserId 'daniel' -Id 'AccessTestCollection'


Final Thoughts

So this is pretty much all there is to managing users and permissions using the Cosmos DB PowerShell module. This module can also be used to manage the following Cosmos DB resources:

  • Attachments
  • Collections
  • Databases
  • Documents
  • Offers
  • Stored procedures
  • Triggers
  • User Defined Functions

You can find additional documentation and examples of how to manage these resources over in the Cosmos DB PowerShell module readme file on GitHub.

Hopefully this will help you in any Cosmos DB automation tasks you might need to implement.


Configure Azure SQL Server Automatic Tuning with PowerShell

One thing I’ve found with configuring Azure services using automation (e.g. Azure PowerShell Modules, Azure Resource Manager template) is that the automation features are a little bit behind the feature set. For example, the Azure PowerShell modules may not yet implement settings for new or preview features. This can be a an issue if you’re strictly deploying everything via code (e.g. infrastructure as code). But if you run into a problem like this, all is not lost. So read on for an example of how to solve this issue.


One of the great things about Azure is that everything is configurable by making direct requests to the Azure REST APIs, even if it is not available in ARM templates or Azure PowerShell.

Depending on the feature/configuration you can sometimes use the Set-AzureRmResource cmdlets to make calls to the REST APIs. But this cmdlet is limited to using an HTTP method of POST. So if you need to use PATCH, you’ll need to find an alternate way to make the call.

So, what you need then is to use the Invoke-RestMethod cmdlet to create a custom call to the REST API. This is the process I needed to use to configure the Azure SQL Server Automatic Tuning settings and what I’ll show in my script below.

The Script

The following script can be executed in PowerShell (of course) and requires a number of parameters to be passed to it:

  • SubscriptionId – the subscription Id of the Azure subscription that contains the Azure SQL Server.
  • ResourceGroupName – The name of the resource group containing SQL Server or
  • ServerName – The name of the Azure SQL Server to set the automatic tuning
    options on.
  • DatabaseNameThe name of the Azure SQL Database to set the automatic tuning options on. If you pass this parameter then the automatic tuning settings are applied to the Azure SQL Database, not the server.
  • Mode – This defines where the settings for the automatic tuning are
    obtained from. Inherit is only valid if the DatabaseName is specified.
  • CreateIndexEnable automatic tuning for creating an index.
  • DropIndexEnable automatic tuning for dropping an index.
  • ForceLastGoodPlan – Enable automatic tuning for forcing last good plan.

Requirements: You need to have the installed the AzureRM.Profile PowerShell module (part of the AzureRM PowerShell Modules) to use this script. The script also requires you to have logged into your Azure Subscription using Add-AzureRmAccount (as a user or Service Principal).

#Requires -Modules 'AzureRM.Profile'
Configure Azure SQL Autotuning on an Azure SQL server
or database.
This function will retrieve a current access token from
the Azure RM PowerShell context and use it to make a direct
request to the Azure management portal endpoint for the
SQL Server or database. It will configure the Autotuning
parameters for the server.
Requires AzureRM PowerShell Modules 5.1.1 or above*.
* May work on lower versions but untested
.PARAMETER SubscriptionId
The Azure subscription Id of the subscription containing
SQL Server or database.
.PARAMETER ResourceGroupName
The name of the resource grou containing SQL Server or
The name of the Azure SQL Server to set the autotuning
options on.
.PARAMETER DatabaseName
The name of the Azure SQL Database to set the autotuning
options on.
This defines where the settings for the Autotuning are
obtained from.
Inherit is only valid if the DatabaseName is specified.
.PARAMETER CreateIndex
Enable autotuning for creating an index.
Enable autotuning for dropping an index.
.PARAMETER ForceLastGoodPlan
Enable autotuning for forcing last good plan.
param (
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
[Parameter(Mandatory = $true)]
[ValidateSet('Auto', 'Custom', 'Inherit')]
$Mode = 'Auto',
[ValidateSet('On', 'Off', 'Default')]
$CreateIndex = 'Default',
[ValidateSet('On', 'Off', 'Default')]
$DropIndex = 'Default',
[ValidateSet('On', 'Off', 'Default')]
$ForceLastGoodPlan = 'Default'
# Get an access token from the Auzre RM PowerShell token cache for accessing the Azure Management Portal
$context = Get-AzureRmContext
$cache = $context.TokenCache
if (-not $cache)
# Use an older method of accessing the Token Cache (for old versions of AzureRM.Profile)
$cache = [Microsoft.IdentityModel.Clients.ActiveDirectory.TokenCache]::DefaultShared
$cacheItems = $cache.ReadItems()
$cacheItem = $cacheItems |
Where-Object -FilterScript { $_.TenantId -eq $context.Tenant.TenantId } |
Select-Object -First 1
if (-not $cacheItem)
Throw ('A current access token could not be found for the tenant Id {0}.' -f $context.Tenant.TenantId)
$accessToken = $cacheItem.AccessToken
# Generate the Body of the request
$body = @{
properties = @{
desiredState = $Mode
options = @{
createIndex = @{
desiredState = $CreateIndex
dropIndex = @{
desiredState = $DropIndex
forceLastGoodPlan = @{
desiredState = $ForceLastGoodPlan
# Generate the URI to the endpoint
$uri = ('{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}' -f $SubscriptionId, $ResourceGroupName, $ServerName )
if ($PSBoundParameters.ContainsKey('DatabaseName'))
$uri = ('{0}/databases/{1}' -f $uri, $DatabaseName)
if ($Mode -eq 'Inherit')
Throw 'Inherit mode is only valid for a SQL database. Either use a different not or specify a database name.'
$uri = ('{0}/automaticTuning/current?api-version=2017-03-01-preview' -f $uri)
$bodyText = ConvertTo-Json -InputObject $body -Depth 10
$headers = @{
'Authorization' = ('Bearer {0}' -f $accessToken)
'Cache-Control' = 'no-cache'
$invokeRestMethodParameters = @{
Uri = $Uri
Method = 'PATCH'
Headers = $headers
ContentType = 'application/json'
Body = $bodyText
return Invoke-RestMethod @invokeRestMethodParameters

Example Usage

To apply custom automatic tuning to an Azure SQL Server:

.\Set-AzureRMSqlServerAutotuning.ps1 -SubscriptionId '<Subscription Id>' -ResourceGroupName '<Resource Group name>' -ServerName '<Azure SQL server name>' -Mode Custom -CreateIndex On -DropIndex On -ForceLastGoodPlan Off


To apply custom automatic tuning to an Azure SQL Database:

.\Set-AzureRMSqlServerAutotuning.ps1 -SubscriptionId '<Subscription Id>' -ResourceGroupName '<Resource Group name>' -ServerName '<Azure SQL server name>' -DatabaseName '<Azure SQL database name>' -Mode Custom -CreateIndex On -DropIndex On -ForceLastGoodPlan Off



I’ve not yet encountered something in Azure that I can’t configure via the Azure REST APIs. This is because the Azure Management Portal uses the same APIs – so if it is available in the portal then you can do it via the Azure REST APIs. The biggest challenge is determining the body, header and methods available if the APIs are not yet documented.

If the API you need is not documented then you can raise a question in the Microsoft Azure Forums or on Stack Overflow. Failing that you can use the developer tools in your browser of choice to watch the API calls being made to the portal – I’ve had to resort to this many times, but documenting that process is something I’ll save for another day.


Create a Scheduled Task with unlimited Execution Time Limit in PowerShell

When creating a scheduled task in PowerShell you may wish to set the Execution Time Limit of the task to be unlimited (no time limit).


This will prevent the task from being terminated if it is still running after a specific period of time.

Creating scheduled tasks using PowerShell is pretty easy using the *-ScheduledTask* cmdlets in Windows Server 2012 and above.

However, after working on this issue in the xScheduledTask DSC resource in the Microsoft DSC Resource Kit I found that there are some differences in how to do this between Windows Server 2012 R2 and Windows Server 2016.

So in this post I’m going to show how to create a scheduled task with no Execution Time Limit that will work on both Windows Server 2012 R2 (and Windows 8/8.1) and Windows Server 2016 (and Windows 10).

I’ll also show the method that works only on Windows Server 2016.

All Versions of Windows Server

To create a scheduled task with unlimited Execution Time Limit on Windows Server 2012 R2 and Windows Server 2016.

$trigger = New-ScheduledTaskTrigger -Once -At '13:00:00'
$action = New-ScheduledTaskAction -Execute 'powershell.exe'
$settingsSet = New-ScheduledTaskSettingsSet
# Set the Execution Time Limit to unlimited on all versions of Windows Server
$settingsSet.ExecutionTimeLimit = 'PT0S'
$task = New-ScheduledTask -Trigger $trigger -Action $action -Settings $settingsSet
Register-ScheduledTask -TaskName 'MyTask' -InputObject $task

This should also work on Windows Server 2012, but I have not confirmed this. It will NOT work on Windows Server 2008 R2. It should also work on Windows 8/8.1/10.

Windows Server 2016 Only

To create a scheduled task with unlimited Execution Time Limit on Windows Server 2016 only:

$trigger = New-ScheduledTaskTrigger -Once -At '13:00:00'
$action = New-ScheduledTaskAction -Execute 'powershell.exe'
# Set the Execution Time Limit to unlimited on Windows Server 2016
$settingsSet = New-ScheduledTaskSettingsSet -ExecutionTimeLimit '00:00:00'
$task = New-ScheduledTask -Trigger $trigger -Action $action -Settings $settingsSet
Register-ScheduledTask -TaskName 'MyTask' -InputObject $task

This method is a more elegant approach and arguably how the Scheduled Task cmdlets are intended to be used. But you would only use this method if your task does not need to created on an operating system earlier than Windows Server 2016/Windows 10.

So, hopefully this will help anyone else out there who has struggled with this.