Can I grant a database user permission to create views in a schema?
sql-server, sql-server-2008
Solution
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
Problem
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!