Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Cannot save a view

Hi there.

One of our users is suddenly not able to save views.

They can right click Views and click New View, add tables and the rest of the steps to build the view. When they go to Save View, it comes up with the following message in the screenshot.

I have looked up this error message and it talks about permissions that have been changed to the database.

I noticed that there is a CREATE VIEW permission which is set for this user under the database.

I even went as far as creating a Login for the server under Security > Logins, which I linked to a Windows Authenticated account. This Login has been added to the database and has the role of db_owner which the user having the issue and I are in, which has grant permissions for everything from the looks of things.

Strangely, the user can create views by executing the code directly, using:
create view <view_name> as <select statement>

Does anyone know what the issue is here?
cannot-create-view.jpg
0
stvmph
Asked:
stvmph
  • 5
  • 2
1 Solution
 
DcpKingCommented:
You state that the user can successfully create a view by opening a New Query window and entering

create view dbo.happiness
as
    select hazelnuts from toffee


When you create a view using the SSMS wizard it makes all sorts of extra display stuff. Some of this may be damaged in some way. To check, drop down view and check the Template Explorer. The Template Browser window should appear to the right. Scroll down and open the template for View | Create View.

You should get this code (but you may not if someone's accidentally altered it):

-- =============================================
-- Create View template
-- =============================================
USE <database_name, sysname, AdventureWorks>
GO

IF object_id(N'<schema_name, sysname, dbo>.<view_name, sysname, Top10Sales>', 'V') IS NOT NULL
	DROP VIEW <schema_name, sysname, dbo>.<view_name, sysname, Top10Sales>
GO

CREATE VIEW <schema_name, sysname, dbo>.<view_name, sysname, Top10Sales> AS
<select_statement, , SELECT TOP 10 * FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC>

Open in new window


If not, copy what's there out into Notepad, replace it with the stuff above, and click on the save icon (the floppy disk).  Hopefully that will now work for you.

hth

Mike
0
 
stvmphAuthor Commented:
Thanks for the response Mike. Unfortunately it is already like that in the template. Without knowing much about them, I feel like templates only affect the profile of the computer being used. I am using SSMS from a terminal server with a test account and my own account in two different sessions.

I have a feeling it has something to do with permissions, as creating views graphically works fine for me. It doesn't work for the test account.
0
 
Alpesh PatelAssistant ConsultantCommented:
There error says, you may trying to alter existing view but view does not exists in database.

So, server throwing the error.


If you were trying to create new the am sure it's permission issue.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DcpKingCommented:
You'd think, Patel, but he can create a view by typing in the straight create view code, just not using the wizard.

stvmph: can your user create a view in a New Query view and then successfully edit it using the wizard, or does that also result in the system refusing to save? If so, it may be a setting in SSMS - ask him if he (or anyone else using that machine while logged on as him) has changed anything in Configuration (Preferences).

Mike
0
 
stvmphAuthor Commented:
I'm not 100% sure it is SSMS configurations either, because he cannot do it from his own PC or the terminal server. I tested out with him.

It's like there is some sort of permission to allow/deny it through the "wizard".
0
 
stvmphAuthor Commented:
It looked as though the user's security (server role) was only set to public and needed to be set to sysadmin as well. Thank you everyone for all of your assistance.
0
 
stvmphAuthor Commented:
I was able to find the solution to my problem which was not related to any of the solutions posted above.
0
 
stvmphAuthor Commented:
Actually I may have prematurely set a solution as it was merely a workaround.

I was told the following on Reddit.

"Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created."
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now