We help IT Professionals succeed at work.

SQL Design for Views is grayed out

Brockstedt
Brockstedt asked
on
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
Comment
Watch Question

Contract ERP Admin/Consultant
Commented:
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.