Azure SQL Database – Get users and role members for all databases

I ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.

This is definitely an early work in progress, but if it helps someone, feel free to take it, adapt it, send me ideas to make it better, and so on.

There are two files as part of this Gist. The SQL file is used to hold whatever code you want to run against each database. The PowerShell PS1 file is used to call that SQL file against each Resource Group, Server, and Database. This is not designed to just “run the script” because you’ll want to adjust the path settings and connect your Azure account. After that, you should be able to run without too much trouble. This could be adapted to work in a Runbook or output to Blob storage instead of a local drive, but this is a basic start.