9

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?

1
  • 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
    Jun 3, 2013 at 20:57

2 Answers 2

9

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];
1
  • 3
    Easy enough. Glad we have a community like this.
    – Wes
    Jun 3, 2013 at 21:08
1

Try this :

EXEC sp_MSForEachTable 'GRANT INSERT, UPDATE ON ? to [ROLE]'
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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