This month, Steve Jones (T) invites us to write about the way dynamic SQL helped us solve a problem.
Case
For one of our customers, we’ve had an interesting case. They have multiple schemas in the database and the schema’s contain a lot of tables with different use cases. To grant user groups (not individual users, never individual users) select rights to tables, we’d usually grant select on a schema.
In this case, that wasn’t possible. The schema contained data from multiple sources that had to stay separated when it comes to reading rights. In other words, a user would be able to read from schema DL.Table1 but not from schema DL.Table2. If it would concern one table, the script would be easy to write and we’d be having coffee discussing why people want access to data. In this case, we were seeing 1100 tables and scripting them by hand would not be fun. Because of the nature of the database, the tables we needed to grant the select rights to all started with the same letter combination, unique to all the other tables in the schema.
Complication
To make it a bit more complicated, because of the number of rows, I can’t get all the rows in one go. At least I can’t. So I reverted to the technique I know that works and, in this case, causes little to no overhead: cursors.
DECLARE @Query NVARCHAR(2000)
DECLARE @tableName NVARCHAR(200)
DECLARE cursed CURSOR FOR
SELECT t.name
FROM sys.tables t
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE s.name = 'demo'
and t.name like 'T%'
OPEN cursed
FETCH NEXT FROM cursed into @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = 'GRANT SELECT ON demo.' + @tableName + ' TO USERGROUP;'
print @query
-- EXEC sp_executesql @query;
FETCH NEXT FROM cursed INTO @tableName
END
CLOSE cursed
DEALLOCATE cursed
Solution
In short, what happens here is that I’m getting all the tablenames from my schema that match the preset definition. When the list is complete (and validated in a separate process), I’m building the grant select on script. To prevent you from copying and running the script without thinking it all through, I disabled the execution. It just prints the results. You’re welcome.
This simple script saved quite some time and gave the AD user group members access to the data they had to without granting access to all the other tables.
Other options?
Shouldn’t this have been solved with different schemas? Probably, but the design didn’t allow for that so we had to work with this setup. Is there a better solution? Probably, but this one was quick and solved the issue. If access has to be revoked, we can check the access of the AD group and deny or revoke the select.
Thanks for reading!
Reitse's blog 
Thank you Reitse for the article!
I like to use sp_MSForEachTable for this type of scripts, but I think your solution has more power and can be used for more complex tasks!
Regards,
Eric
LikeLike
Security hint: it would have cost you just one second to write QUOTENAME() around the table name in the SET @Query but could save you tons of fixing time is someone used bad / uncommon table names (sql injection or simply a space somewhere in the table name – in worst case at the end…)
Regarding the cursor: it is on the long term more fail save to always use the following WHILE loop in a cursor.
WHILE 1 = 1
BEGIN
FETCH NEXT FROM CurName INTO @dummy;
IF @@fetch_status 0 BREAK;
— DoSomething
END;
Often you add later another variable and have – in your version – to fix at least two FETCH statements. Or you add some sort of check later where you use CONTINUE – if you forget here another FETCH you will be stuck in a infinitive loop (and have now at least 3 FETCHs to be maintained). With “my” version you will always have only a single FETCH and don’t have to care about CONTINUE statements.
LikeLike
— No cursors and no use of undocumented calls.
DECLARE @Query NVARCHAR(MAX) = ‘SET NOCOUNT ON;’;
SELECT
@Query = @Query + ‘GRANT SELECT ON ‘ + QUOTENAME(S.name) + ‘.’ + QUOTENAME(T.name) + ‘ TO UserGroup;’ + CHAR(13) + CHAR(10)
FROM sys.tables AS T
JOIN sys.schemas AS S ON T.schema_id = S.schema_id
WHERE S.name = ‘demo’
AND T.name LIKE ‘T%’;
— Note that PRINT will only show the first 8k of a Varchar (4k for NVarchar).
— I use code from here to get around this.
— https://www.codeproject.com/Articles/18881/SQL-String-Printing
PRINT @Query;
–EXEC sp_executesql @Query;
LikeLike