(Note: I loved the code I found on the original posting, but it would not work without a simple change. I’m copying the post here and highlighting my changes, talk about a time saver!)
Granting Select to all tables for a login can be an extremely painful and lengthy process. Utilizing the SQL Server Management Studio to perform this means going table by table and checking the Select checkbox for each individual table, this makes a real nightmare if you are working with hundreds of tables. You can do this in mere seconds versus hours by using the Query window. Here’s how:
1. Open SQL Server Management Studio.
2. Connect to the desired Server.
3. Click the New Query button.
4. Select the desired database from the dropdown in the toolbar.
5. To grant Select to all tables in the database, copy and paste the following into your Query window:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
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%'
DECLARE @rowid int, @sqlstr varchar(500)
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_tbl_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @tables
ORDER BY ROWID
OPEN grant_tbl_cursor
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_tbl_cursor
DEALLOCATE grant_tbl_cursor
6. In the second line of the pasted query, change loginname to the login that you wish to grant Select to all tables.
7. Execute the query.
To grant Select to all Views in the database, follow the above steps but use the following:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
DECLARE @views TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @views
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'V'
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_vw_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @views
ORDER BY ROWID
OPEN grant_vw_cursor
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_vw_cursor
DEALLOCATE grant_vw_cursor
Why go through all of that hassle instead of using an out-of-the box role assignment?
USE [Database]
alter role db_datareader add member […]
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15
Thanks Josh!
I made one small change to allow for schemata other than dbo .
…
SELECT ‘GRANT SELECT ON ‘ + schemas.name + ‘.’ + sysobjects.name + ‘ TO ‘+@login
FROM sysobjects INNER JOIN sys.schemas ON sysobjects.uid = schemas.schema_id
…