Introducing sp_AzSQLDBPermissions (Beta)


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.

9 thoughts on “Introducing sp_AzSQLDBPermissions (Beta)

  1. Chad Estes says:

    How about auto-generating a strong password to insert into the create statement?
    I’ve used the following code below in a couple of routines and have made it quite configurable:


    @maxPWlen tinyint=40, –Maximum length of password used {should be at least 2x sum(@req* variables below)}
    @minPWlen tinyint=32, –Minimum length of password used
    @reqSym tinyint=5, –Required number of Symbols in password
    @reqNbr tinyint=5, –Required number of Numbers in password
    @reqUCL tinyint=5, –Required number of Upper Case Letters in password
    @reqLCL tinyint=5, –Required number of Lower Case Letters in password
    ————————————- CONFIGURATION PARAMETERS ABOVE THIS LINE ———————————————-
    @cntSym tinyint=0, –Count of Symbols used in password
    @cntNbr tinyint=0, –Count of Numbers used in password
    @cntUCL tinyint=0, –Count of Upper Case Letters used in password
    @cntLCL tinyint=0, –Count of Lower Case Letters used in password
    @rand int,
    @class int,
    @password nvarchar(256)

    DECLARE @table as TABLE (id tinyint identity(1,1) NOT NULL, lowerbound tinyint NOT NULL, upperbound tinyint NOT NULL, ctype tinyint NOT NULL);
    INSERT INTO @table (lowerbound,upperbound, ctype) –Create a table of ANSI characters grouped into quasi-equal sub-classes
    SELECT lowerbound, upperbound, ctype
    FROM (–39 is a single open-quote; avoid using to prevent character escaping issues.
    SELECT 33, 38, 1 UNION ALL –symbols
    SELECT 40, 47, 1 UNION ALL –symbols
    SELECT 58, 64, 1 UNION ALL –symbols
    SELECT 91, 96, 1 UNION ALL –symbols
    SELECT 123, 126, 1 UNION ALL –symbols
    SELECT 48, 52, 2 UNION ALL –numerals
    SELECT 53, 57, 2 UNION ALL –numerals
    SELECT 97, 105, 3 UNION ALL –lowercase letters
    SELECT 106, 114, 3 UNION ALL –lowercase letters
    SELECT 115, 122, 3 UNION ALL –lowercase letters
    SELECT 65, 73, 4 UNION ALL –uppercase letters
    SELECT 74, 82, 4 UNION ALL –uppercase letters
    SELECT 83, 90, 4 –uppercase letters
    ) AS A (lowerbound, upperbound, ctype)

    SET @password =”;SET @cntSym=0;SET @cntNbr=0;SET @cntLCL=0;SET @cntUCL=0;
    WHILE (LEN(@password) < @minPWlen OR @cntSym < @reqSym OR @cntNbr < @reqNbr OR @cntLCL < @reqLCL OR @cntUCL < @reqUCL)
    SELECT @rand = CAST(ROUND((MAX(id)-MIN(id)) * rand() + MIN(id), 0) AS integer) FROM @table
    SELECT @rand = CAST(ROUND((upperbound-lowerbound) * rand() + lowerbound, 0) AS integer), @class = ctype FROM @table WHERE id = @rand
    IF (@class = 1) SET @cntSym = @cntSym + 1
    IF (@class = 2) SET @cntNbr = @cntNbr + 1
    IF (@class = 3) SET @cntLCL = @cntLCL + 1
    IF (@class = 4) SET @cntUCL = @cntUCL + 1
    IF (LEN(@password) = @maxPWlen)
    SELECT @class = Ctype FROM @table WHERE ASCII(LEFT(@password,1)) BETWEEN lowerbound AND upperbound
    IF (@class = 1) SET @cntSym = @cntSym – 1
    IF (@class = 2) SET @cntNbr = @cntNbr – 1
    IF (@class = 3) SET @cntLCL = @cntLCL – 1
    IF (@class = 4) SET @cntUCL = @cntUCL – 1
    SELECT @password = RIGHT(CONCAT(@password,CHAR(@rand)),@maxPWlen)

    Just concatenate @password into your create statement. Of course you may need to work some looping magic to generate multiple passwords to prevent your create statements from having the same password.

  2. Jason says:

    You could insert something in for the new password that the system would reject so people have to replace it. Not sure what that would be. Hmmm. :Heads off to BOL:

  3. […] 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 […]

Leave a Reply to Viewing Azure SQL DB security with sp_AzSQLDBPermissions | SQL Studies Cancel reply

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

You are commenting using your 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,142 other followers

Follow me on Twitter

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