Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
Yes, unfortunately in our case we have thousands of tables in this database, so I'm hoping for a database level approach that won't require much maintenance. –  Sam Storie Dec 4 '12 at 16:52
    
Would being able to script the deny statement be adequate or are you looking for more? And I'm assuming denying access on the views and then creating a DDL statement to enforce it on new views also wouldn't work for you. –  cfradenburg Dec 4 '12 at 17:09
    
That might be workable, and I appreciate the idea, but it would not be ideal. The desired state is one where a group of 5-10 people would retain the ability to create and alter views, but another set of 20-25 people need to have that permission denied. Unfortunately, we don't have good controls over what is being created either. Could some sort of DDL trigger catch new views and ensure the permissions are maintained automatically? –  Sam Storie Dec 4 '12 at 17:24
    
What about a DDL trigger for alter that would check the object type that's being altered and if it's a view then check the username and if it's not an approved one raise an error? You should even be able to check sys.login_token to see if they're in a specific domain group instead of maintaining a list of names in the trigger. I'll edit my answer to mention that. –  cfradenburg Dec 4 '12 at 17:36
    
This approach is more complex than I would like, but provides options for working around SQL Server's available permissions in this case. Unless someone can shed more light on how this could be done with the standard permissions I'll take this answer. Thank you. –  Sam Storie Dec 4 '12 at 17:50
add comment

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.

share|improve this answer
4  
CREATE VIEW encapsulates creation and alteration: that's incorrect. As owner of the view created you have all the possible permissions on the view. But that does not preclude you from having ALTER VIEW permission on other views, ones not owned. OP has to deny both CREATE and ALTER. –  Remus Rusanu Dec 4 '12 at 15:21
    
You are correct. What's interesting is that MSDN implies that this denying create should then deny alter (msdn.microsoft.com/en-us/library/ms177518.aspx), but it is not the case. –  Kevin Feasel Dec 4 '12 at 16:15
    
So if I'm understanding this correctly, I'd still need to deny the ALTER command on a schema, which would impact more than just views? –  Sam Storie Dec 4 '12 at 16:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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