Create database role to allow read access to all tables; write access to some

Question

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?

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 03 '13 at 20:57

Aaron Bertrand · Accepted Answer · 2013-06-03T21:04:59.800

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];

edited Jun 03 '13 at 21:04

answered Jun 03 '13 at 20:57

Aaron Bertrand

175,645
25
380
589

3

Easy enough. Glad we have a community like this. – Wes Jun 03 '13 at 21:08

score 1 · Answer 2 · answered Jun 03 '13 at 21:07

1

Try this :

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

answered Jun 03 '13 at 21:07

druzin

2,228
10
15

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`](http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/). – Aaron Bertrand Jun 03 '13 at 21:11

Create database role to allow read access to all tables; write access to some

2 Answers2