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?
Use GRANT statement with tables delimited by comma like below.
Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];
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
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.
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
