Can I grant a database user permission to create views in a schema?

Question

I'd like to set up a security situation where my database user ("ApplicationUser") has permission to create, alter and drop views within a schema.

If I execute the statement

GRANT ALTER ON SCHEMA :: MySchema TO ApplicationUser;

And then connect to the database and execute the statement:

CREATE VIEW [MySchema].[MyView] AS SELECT * FROM SomeTable

I get the error message

CREATE VIEW permission denied in database 'MyDatabase'.

Is it possible to configure security the way I want, or must I grant "ALTER" on the whole database? Or something else?

Thanks for your help!

Answer 1

create schema myschema authorization ApplicationUser
GO

grant create view to ApplicationUser
GO

To do this you need to either change the authorization of the schema, which may have other consequences, or use something like a database DDL trigger.

For an existing schema do:

alter authorization on schema::myschema to ApplicationUser

Like I say, this way should be done with caution. For the other way http://msdn.microsoft.com/en-us/library/ms186406(v=sql.105).aspx