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

posted @ 2018-06-28 10:42  rosewong  阅读(111)  评论(0编辑  收藏  举报