June 27, 2019 by Kenneth Fisher
I’ve written a version of my permissions scripts for Azure SQL Database. It’s still in Beta but I’m confident enough in it that I’m willing to share. I’m only putting it out on Github right now. I’ll add it to my site when I’ve got it fully released. Regardless, for now, if you are interested in trying it out and maybe letting me know of any bugs you find you can find it here:
Obviously, it’s going to have a few differences from the normal sp_DBPermissions.
- 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 Here>’. Please don’t actually use that as a password! (Yes, I’m aware <Insert Strong Password Here> is about to become a popular password, I just have no idea how to avoid it.)
Otherwise, it should work the same as the other SPs.