Sql-server – Create database role to allow read access to all tables; write access to some

roleSecuritysql-server-2012

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?

Best Answer

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

Related Solutions

SQL Server 2008 – How to Assign Active Directory Group Security Access

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
Add user to role (also a "database level principal")
GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.

Sql-server – Adding Security to a legacy DB with Schema

This sounds like a reasonable plan
DENY is tricky to get right and may not work anyway See:

Just in case you missed it, You can GRANT .. SCHEMA so you don't have to GRANT per object:

GRANT SELECT ON SCHEMA::dbo TO RoleGeneralReader

Best Answer

Related Solutions

SQL Server 2008 – How to Assign Active Directory Group Security Access

Sql-server – Adding Security to a legacy DB with Schema

Related Question