Grant permissions to multiple tables in a database
USE DB
GO
DECLARE @TableName AS varchar(max)
DECLARE @Script AS varchar(max)
DECLARE CurGrant CURSOR FOR
SELECT name
FROM sys.tables
WHERE name LIKE '%Sales_%'
OPEN CurGrant
FETCH NEXT FROM CurGrant
INTO @TableName
WHILE @@FETCH_STATUS = 0 ---The FETCH statement was successful.
BEGIN
SET @Script = 'GRANT SELECT ON dbo.' + @TableName + ' TO [loginname]'
EXEC (@Script)
FETCH NEXT FROM CurGrant
INTO @TableName
END
CLOSE CurGrant
DEALLOCATE CurGrant