A few months back I put the Azure SQL DB version of my permissions script into beta. I’m not sure how much it’s been used by anyone other than me but so far it appears to be working correctly. I do have a few issues on GitHub but nothing that’s stopping me from officially releasing sp_AzSQLDBPermissions. As with the other scripts it’s located in the drop down menu under free scripts.
When I announced it into beta I pointed out a few differences from the normal sp_DBPermissions. They haven’t changed much but here is the list again just in case.
- There are no cross-database calls so there’s no point in having an @DBName parameter and no point in displaying the database name in the output.
- For basically the same reason I’ve removed the @LoginName parameter and the Server_Principal column in the first rowset.
- I’ve added support for the EXTERNAL PROVIDER type.
- I haven’t been able to find a way to get the password hash (and there doesn’t appear to be support for it in the CREATE USER statement anyway) so the CREATE script just says PASSWORD = ‘<insert strong password> newid()’. It’s going to be a strong password, and you won’t have duplicates so if you want to use it feel free. Probably still better to generate your own though.
- This is for Azure SQL DB only. It will not work on Azure Datawarehouse. There appear to be some limitations there that I haven’t been able to workaround. I’ll look again over time and maybe I’ll be able to figure out a way past this.
Otherwise, it should work the same as the other SPs.