Viewing Azure SQL DB security with sp_AzSQLDBPermissions

2

December 18, 2019 by Kenneth Fisher

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.

2 thoughts on “Viewing Azure SQL DB security with sp_AzSQLDBPermissions

  1. blobeater says:

    Very cool, adding to my script list.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,152 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: