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