Introducing sp_AzSQLDBPermissions (Beta)
9June 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.
SQL Studies 
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.
Nod a bad idea. Although honestly I’d probably use newid() for something like this. Maybe add that to the end of the current string.
Using NewID() as your password would probably work too. I had requirements that had to be met that NewID() didn’t satisfy for PCI related passwords.
*nod* My only requirement here is that I don’t want everyone who uses the SP to have the same password 😀
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:
Looks like you could exceed 128 characters or start the password with an @ or a $ without escaping them. The docs at least say that breaks the rules for passwords, so I would expect it to toss an error.
I do like the elegance of newid() better though. 🙂
That and honestly, if you don’t care (i.e. never going to actually use it), a GUID is just fine as a password.
[…] 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 […]