1

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.

Improve this question

2 Answers 2

Reset to default
5

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?

Improve this answer
2
  • A couple of things. First, I think I was on the right path. I had tried a command similar to the first one you mentioned but it did not work initially. The problem was due to a bonehead mistake in that I forgot to add the user to the DB role that I was granting the permission to. Second, the second statement did not work for me. It looks like I have to grant the CREATE VIEW permission at the database level and then grant the ALTER permission to a specific schema. I think this will work as I want. Anyhow, I have marked your answer as the accepted solution.
    – Jason Richmeier
    Feb 9, 2018 at 19:58
  • Thanks for your feedback. Once you figure it out, feel free to update my answer with the solution.
    – Randolph West
    Feb 9, 2018 at 20:39
0

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.

To be able to create/alter/drop views in Reporting schema only you should grant CREATE VIEW permission in the database and ALTER permission on the schema:

grant CREATE VIEW to user1;
grant ALTER on schema::Reporting to user1;

Note, you CANNOT execute this one:

 GRANT CREATE VIEW ON SCHEMA :: Reporting TO user1;

You'll get the following error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'CREATE VIEW'.

This is because CREATE VIEW can be granted on database level only.

The same thing you'll find in the BOL article: CREATE VIEW (Transact-SQL):

Permissions

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created

When you grant these 2 permissions, they are aslo sufficient to alter and drop any view in the schema Reporting, so if you wanted your user just to be able to create/alter/drop views in Reporting schema only you've done.

There is no need to have SELECT permission in dbo schema if all you want is to make him create views.

If you want to make your user be able to SELECT from views created (in schema Reporting) you should grant SELECT on schema Reporting:

grant select on schema::reporting to user1;

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.

grant insert, update, delete on dbo.Report to user1

To be able to update / delete using filter you should also grant select on dbo.Report, so your third grant should be this one:

grant select, insert, update, delete on dbo.Report to user1
Improve this answer

Your Answer

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

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