Introducing sp_AzSQLDBPermissions (Beta)

8

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:

sp_AzSQLDBPermissions

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.

8 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:

    SET NOCOUNT ON;

    DECLARE
    @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)
    ORDER BY NEWID()

    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)
    BEGIN
    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)
    BEGIN
    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
    END
    SELECT @password = RIGHT(CONCAT(@password,CHAR(@rand)),@maxPWlen)
    END

    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:

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 2,530 other followers

Follow me on Twitter

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