CODEWITHSUNDEEP
sql-serversql-server-2008
James Orr
James Orr

Reputation: 5135

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

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!

Upvotes: 9

Views: 60365

Answers (2)

Wichie Artu
Wichie Artu

Reputation: 125

I was able to do this by...

  1. Granting a create view/select permission on the database to a role
  2. Grant alter permissions to that role for the schemas I want to have views
  3. Deny alter permission to that role for the schemas I didn't want to have views

Here was my syntax...

USE [database];
CREATE ROLE [role];
GRANT SELECT TO [role];
GRANT CREATE VIEW TO [role];
GRANT ALTER ON SCHEMA::[schema 1] TO [role];
DENY ALTER ON SCHEMA::[schema 2] TO [role];

--TEST 
/*
EXECUTE AS USER = '[user w/role]';
CREATE VIEW [schema 1].test AS (select 'test' as 'test');
DROP VIEW [schema 1].test
CREATE VIEW [schema 2].test AS (SELECT 'test' AS 'test');
DROP VIEW [schema 2].test 
REVERT
*/

Upvotes: 6

muhmud
muhmud

Reputation: 4604

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

Upvotes: 12

Related Questions