Create user defined database role for create and alter views

Question

Post reply

Create user defined database role for create and alter views

ravisamigo

SSCertifiable

Points: 7440
More actions
April 2, 2012 at 12:00 am

#271263

Hi All,
How to create a user defined database role to give create and alter permissions only on views.
Please advise.
Thanks and Regards,
Ravi.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

create the role then grant CREATE VIEW to the role this will also imply ALTER as well

ravisamigo SSCertifiable Points: 7440 More actions · Answer 2

Thanks for your reply.

I have tried this but still not able to create view/alter view...throwing the below errors

Cannot alter the view 'v_xxxxx', because it does not exist or you do not have permission.

The specified schema name "dbo" either does not exist or you do not have permission to use it.

USE TestEcom

go

CREATE ROLE db_viewcreator

GO

GRANT CREATE VIEW TO db_viewcreator;

GO

EXEC sp_addrolemember N'db_viewcreator', N'testuser'

GO

Please check and advise.

Thanks and Regards,

Ravi.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

you need to grant alter on the schema that you want the view to be created in to the role. if you have just given CREATE VIEW to the role then thats all they can do, they wont be able to create tables etc.

ravisamigo SSCertifiable Points: 7440 More actions · Answer 4

If I grant the alter, it will apply to all the objects but I need only for views.

GRANT ALTER TO db_viewcreator;

GO

Please advice me if I'm wrong.

Thanks and Regards,

Ravi.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

GRANT ALTER ON SCHEMA::dbo TO db_viewcreator

This will say they have permission to alter the schema, but SQL needs to know what it can alter and as you have only given CREATE VIEW it can only alter and create views.

Lowell SSC Guru Points: 323518 More actions · Answer 6

ravisamigo (4/2/2012)
Hi All,
How to create a user defined database role to give create and alter permissions only on views.
Please advise.
Thanks and Regards,
Ravi.

Ravi take a look at this thread on the same theme of a role for view creation, and the proposed solution i put at the end:

http://www.sqlservercentral.com/Forums/Topic1241211-391-1.aspx

I thought the role needed a lot more than CREATE VIEWs because of the requirement to alter other views as well, so i went the route of giving db_DDLAdmin, and then a DDL trigger to limit it's usage to VIEWS only.

see if that is close to what you want.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!