Changing privileges on multiple SQL server tables for a user

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)

-- ==========
-- Initialize 
-- ==========

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.