SQL Server 2005: Easily Grant Select to All Tables and/or Views in a Database

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 @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 @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

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

10 Responses to “SQL Server 2005: Easily Grant Select to All Tables and/or Views in a Database”

  1. December 01, 2008 at 1:57 am, Craig said:

    Very helpful! Thanks!

    Reply

  2. February 18, 2009 at 8:22 pm, Marc Jellinek said:

    … or you could just add the login to the db_datareader role, which grants select permission to all tables and views within the database.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d71fca8-ad8d-49c5-b4cc-c1cdab0fab43.htm

    Reply

  3. February 26, 2009 at 4:24 pm, Sudarshan said:

    Extremely handy script! exactly what I was looking for. Saved a lot of time.

    Reply

  4. December 02, 2009 at 2:09 pm, Anonymous said:

    Perfect! only wanted user to access tables and not views so adding to the db_datareader role was not an option. Thanks!

    Reply

    • January 08, 2010 at 1:13 pm, Anonymous said:

      But you can grant select on database level ? Or even to a role and then subsequently to a user which is even neater

      grant select on database::Adventureworks to LimitedUser;

      Reply

      • May 07, 2010 at 8:49 am, Mustafa said:

        This is what i was looking for – granting select at database level…worked perfectly, thanks.

        Reply

  5. January 08, 2010 at 6:03 pm, Anonymous said:

    very helpful!

    Reply

  6. April 05, 2010 at 4:33 pm, Anonymous said:

    This is exactly what I needed. Thank you Thank you….. You saved me a ton of time.

    Reply

  7. May 04, 2011 at 9:04 pm, Paul Bankston said:

    doesn’t seem to work with Windows authtenticated users. For example
    ‘domainusername’

    Reply

  8. May 05, 2011 at 7:05 pm, Johnboulom said:

    doesn’t work for other schema other than dbo, e.g., otherschema.table1.
    only work if your tables are part of dbo, e.g., dbo.table1.

    Reply

Leave a Reply