Grant Select for User on All Tables in DB (SQL)

--//Specify DB
USE SaidDB

DECLARE

@login varchar(50)
,@GrantSelectAll VarChar(MAX)

--//Specifiy User to grant permissions to
SELECT @login = 'username'

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @tables
SELECT 'GRANT SELECT ON [' + NAME + '] TO ['+@login +']'
FROM sysobjects
WHERE TYPE = 'U'
AND NAME NOT LIKE 'SYNC%'

SELECT @GrantSelectAll =
(SELECT SQLSTR + CHAR(10)
FROM @tables
FOR XML PATH(''));

PRINT @GrantSelectAll
--// Uncomment when satisfied
--// EXEC(@GrantSelectAll)

Comments :

0 comments to “Grant Select for User on All Tables in DB (SQL)”