Go to Top

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

(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:

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:

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.