Setting Select Permission on 100+ tables in a database

Question

I got the 'Select permission denied' error for some of my tables in a database.

In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties > Permissions > Select the User/Role and tick the Select Permission in the 'Explicit' tab below.

But I want to do that for over a 100 tables. Is there a better way to do it?

Answer 1

If the tables were in a user defined schema, say data and not the default dbo then you can GRANT select on the schema

GRANT SELECT ON SCHEMA::Data TO MyRoleOrUser

I regard this as best practice. It also means any new tables inherit SELECT permisisons from the schema and don't need explicit grants

• SQL Server: hierarchy of permissions for schema?
• Decision criteria on when to use a non-dbo schema vs a new Database
• Schema design - best practices?

Answer 2

There are a couple of alternatives.

You could set the Select permission to the Schema that owns the tables. (dbo?)

grant select on schema :: dbo to myUser --or myDBRole

or use query to generate the 100 grant select statements and execute.

SELECT 'grant select on ' + TABLE_NAME + ' to myUser' --or myDBRole
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

Answer 3

Use GRANT statement with tables delimited by comma like below.

Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];

Answer 4

Run this query:

select 'Grant select on ['+t.name+'] to [MyRole];' from sys.tables as t;

Copy the output to a new SSMS tab, edit as required and submit for processing.