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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.