Some things were easier in the good old days before Dyn365 and Azure and some are better. Often the things that were better before tends to be things we need to relearn.
Allowing firewall access to Azure SQL (for example regarding BI) is easily scripted and here are 3 useful scripts for that purpose:
List current firewall rules:
SELECT * FROM sys.firewall_rules
If you need to grant access to the IPs 22.214.171.124 to 126.96.36.199:
EXEC sp_set_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '188.8.131.52', '184.108.40.206';
Getting rid of the above rule is just as simple:
EXEC sp_delete_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'
The three above is all server rules so you need to run them towards the master database.
You can do the same tricks on database level:
SELECT * FROM sys.database_firewall_rules
EXEC sp_set_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '220.127.116.11', '18.104.22.168';
EXEC sp_delete_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'
Remember to be run them on the desired database.
Please keep in mind that you are messing with security and I – as usual – don’t take any responsibility in any damage you might make by using the above.
This is not really a Dynamics AX thing but with BI in mind it kind of relates.
The thing is that sometimes you have users that needs access to AX tables and views on the SQL server to maintain and develop BI. And often these users should have access to almost every table except a few. Payroll tables are a good example.
In a normal situation you would use schemas to handle this but since AX creates all tables as part of the dbo schema this is not an option. So I created this little script for a colleague to accelerate the proces:
DECLARE @sqlStatement NVARCHAR(max)
DECLARE @user NVARCHAR(30)
DECLARE @filter NVARCHAR(10)
SET @sqlStatement = '';
SET @user = 'contoso\abc';
SET @filter = 'payroll%';
-- Get REVOKE lines
SELECT @sqlStatement = @sqlStatement + 'REVOKE SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V')
-- Get GRANT lines
SELECT @sqlStatement = @sqlStatement + 'GRANT SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V') AND NAME NOT LIKE '' + @filter + ''
-- Execute privilege change query
EXECUTE sp_executesql @sqlStatement;
What it does is that first it creates 3 variables. One for an SQL statement, one for the user identity and one for filtering what tables NOT to grant access to. In this case we want user ABC in the contoso domain to have SELECT rights on all tables except those beginning with “Payroll”.
It then starts by creating the REVOKE SELECT statements for all tables and views (User tables only) and adds them to the @sqlStatement variable.
Then it traverses through the tables and views to make the GRANT SELECT part on all tables NOT matching the filter in the @filter variable.
Finally the created statement is executed using the sp_executesql stored procedure.
It is not the fastest statement in the world; but it gets the job done.