SQL Server Database Role to Create Views

SpaceCoastLife
SpaceCoastLife used Ask the Experts™
on
Can someone tell me the minimum database role that is required in order for a user to be able to create/edit views?  I'm trying to avoid db_owner is possible.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
There is no default role to  create/alter/delete views in sql server.

modifying/creating view requires Alter permission on the schema. You need to create a new schema and provide access to that. Also create DDL triggers so that people cannot drop accidentally these.
Commented:
1. Create a database role  let say. ROLE-1

2. grant permission to that role
   
GRANT CREATE VIEW TO ROLE-1;
GRANT SELECT ON SCHEMA::dbo TO ROLE-1;  
GRANT ALTER ON SCHEMA::dbo TO ROLE-1;

3.  Add users who need to create view into new role "ROLE-1"
EXEC sp_addrolemember 'ROLE-1', 'USER';

Author

Commented:
How do you create a database role, like ROLE-1?  I was going to call it db_createviews - would that be acceptable?
Commented:
use < your database name>
go
CREATE ROLE [db_createviews] AUTHORIZATION [dbo];

or you can do it using GUI,.

1. connect sql server  > expand database in which you want to create role > expand security folder > expand role > right click on database role > create new role  
next steps are self explanatory
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
I was going to call it db_createviews - would that be acceptable?
As long the name doesn't exist you can call it whatever you want.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial