Wildcards in SQL server GRANT statements

[moosemaimer]

Complete and utter SQL noob. We have an application that talks to MSSQL, and I'm trying to add a read-only user so someone can run a separate program that talks to the database. I figured out how to attach a user to the database, and GRANT SELECT ON <TABLE> TO [USER] or DENY ALL ON <TABLE> TO [USER], but there's like a hundred tables in this DB and if I try to do this a hundred times I'm inevitably going to miss something. Related tables all have the same first two characters (SO*, PR*, MI*, etc), so can I use wildcards to do that, or would I be better off trying to script it so it runs on each table in turn?

 

[moosemaimer]

I started out with that, but it looks like by default it grants the SELECT permission on tables, and there's a few where I don't want them to have even that.

Looks like I'll have to solve this with regular expressions programming!

 

[moosemaimer]

Was putting together a query, got as far as SELECTing all the requisite tables and was trying to figure out how to assemble them into a variable when I got the slightest bit frustrated and resorted to asking CoPilot. After telling it the first try was wrong and combining it with what I already had, rate this jank on a scale of mY fRiSt ScIrPt to F this job!

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql += N'GRANT SELECT ON ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' TO [test.user];'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME  LIKE 'Product%'
    OR TABLE_NAME  LIKE 'Sales%';
EXEC sp_executesql @sql;

not real tables, obs