0

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?

0

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

0

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
0

Use GRANT statement with tables delimited by comma like below.

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.