SQL Design for Views is grayed out

Good Day,

We have a user(s) who needs to create and design views and queries. We need this accomplished without granting user(s) DBO rights.

Currently when they right-click a view, the Design menu option is grayed out. What permissions do we need to grant users for this to be available to them.

Thanks,

Dave
BrockstedtAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
You'd have to do the following:

GRANT ALTER ON SCHEMA::dbo TO YourSQLUser
GRANT EXECUTE ON SCHEMA::dbo TO YourSQLUser
GRANT DELETE ON SCHEMA::dbo TO YourSQLUser
GRANT CREATE VIEW  TO YourSQLUser

The problem is, this gives them power to ALTER tables as well.

A better solution might be to set them up their own SCHEMA, put some tables in it, that perhaps ALIAS to other tables, and let them work in that SCHEMA alone. Otherwise, you're at risk.

I have never done this in a production environment. There are lots of safe alternatives, including BI and reporting packages, giving them Reporting Services, read-only access and Excel, etc.
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.