SQL Server – Implementing Granular Security

sql server

Consider a database with the following structure:

Tables

[dbo].[Customer], [dbo].[Address], and [dbo].[Report]

Schemas

[dbo] and [Reporting]

What I would like to be able to do is to have a principal (could be user or role) be able to manage views (CREATE, ALTER, DROP) in the Reporting schema only. Further, this principal would need SELECT permission on all of the tables in the dbo schema (to support the creation of views in the Reporting schema). The last thing I would like to be able to do is give INSERT, UPDATE, and DELETE permissions to the Report table only in the dbo schema.

Is this something that is possible? I have yet to find the right combination of permissions. I can use any combination of users, roles, etc. in order to accomplish this.

Best Answer

Using the GRANT Shema permissions on my made-up ReportUsers domain group, I can assign SELECT permissions as follows:

GRANT SELECT ON SCHEMA :: dbo TO [domain\ReportUsers];

For the Reporting schema, that can be tackled in a few ways. Do you want to give them full control over the schema? Is it just for creating views?

Related Question