SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?

Question

I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group is mapped to a role in the database, so I'm working on tweaking the permissions that role has.

From the documentation and Management Studio it's straightforward to deny the CREATE VIEW permission at the database level. I am also familiar with denying the alter command at the schema level, but that would prevent the altering of tables as well as views. I'm hoping to just deny the ALTER VIEW permission at the database or schema level.

Is this possible?

Answer 1

You could deny access to each individual view. The downside here is that any new views would allow alter unless you had a mechanism to deny permission such as a DDL trigger.

Alternatively, you can revoke the alter command at the schema or DB level (this may require removing a DB role) and then grant access individually to tables via some mechanism.

Another option would be to create a DDL trigger on all alter events. That trigger would check to see if the object being altered is a view via EVENTDATA and if it is make sure the logged in individual is someone with access either by a list of names or using sys.login_token to check for a domain group. If they're not then an error can be raised.

Answer 2

CREATE VIEW encapsulates creation and alteration. Here's a sample script using a login named test mapped to the current database:

grant create table to test
grant create view to test

execute as user='test';

--succeed
create table t ( Id int );
create view v as select * from t;

revert;

deny create view to test;

execute as user='test';

--fail:  permissions error
alter view v as select * from t;

revert;

In the first step we grant create table & view to test. Executing as test, we're able to create a table and a view. Then, we revert back to the real account and deny create view for the test user. When we try to alter the existing view, we get a permissions error: Cannot alter the view 'v', because it does not exist or you do not have permission.