Wildcards in SQL server GRANT statements

Jump to latest Follow Reply
moosemaimer

moosemaimer

Ars Scholae Palatinae
717
  • #1
    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

    moosemaimer

    Ars Scholae Palatinae
    717
  • #5
    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!

    SQL: 
    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
     
    Ardax

    Ardax

    Ars Legatus Legionis
    19,076
    Subscriptor
  • #6
    That, uh, just might work? I'm honestly not sure how well the list of rows will concatenate like that into a single SQL statement for sp_executesql.

    If it doesn't, you'll have to write a cursor for the SELECT ... FROM INFORMATION_SCHEMA.TABLES bit and then build statements inside the loop and execute them.

    Throw it inside a BEGIN TRANSACTION and ROLLBACK and find out.
     
    You must log in or register to reply here.
    Jump to latest Follow Reply
    Jump to latest Follow Reply