SQL Server Database Role to Create Views

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.
SpaceCoastLifeAsked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
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';
1
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
SpaceCoastLifeAuthor Commented:
How do you create a database role, like ROLE-1?  I was going to call it db_createviews - would that be acceptable?
0
 
Deepak ChauhanSQL Server DBACommented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.