Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables.

I was messing around with database roles, but whenever I went to add the tables, I had to hand select all 1000... is there a better way to do this?

share|improve this question
    
That actually might be the simplest way... Is there a way to script that? I need to keep a set of scripts for whenever we refresh one of these databases, I can get all setting back instantly. –  Wes Crockett Jun 3 '13 at 20:57

2 Answers 2

up vote 7 down vote accepted

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly:

GRANT SELECT ON SCHEMA::dbo TO [role];
GO

GRANT INSERT, UPDATE --, DELETE
  ON dbo.table_they_can_write_to TO [role];

DENY SELECT ON dbo.table_they_cannot_read TO [role];
share|improve this answer
2  
Easy enough. Glad we have a community like this. –  Wes Crockett Jun 3 '13 at 21:08

Try this :

EXEC sp_MSForEachTable 'GRANT INSERT, UPDATE ON ? to [ROLE]'
share|improve this answer
3  
FYI this gives write but no read access to all the tables, and also ignores schema altogether. Also sp_MSForEachTable is undocumented, unsupported, and possibly suffers the same skipping issues as sp_MSForEachDB. –  Aaron Bertrand Jun 3 '13 at 21:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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