Solved

Cannot save a view

Posted on 2013-06-19
8
300 Views
Last Modified: 2013-07-07
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
Comment
Question by:stvmph
  • 5
  • 2
8 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39261358
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
 

Author Comment

by:stvmph
ID: 39261420
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39261939
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 39264834
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:stvmph
ID: 39270279
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
 

Accepted Solution

by:
stvmph earned 0 total points
ID: 39295496
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
 

Author Closing Comment

by:stvmph
ID: 39305114
I was able to find the solution to my problem which was not related to any of the solutions posted above.
0
 

Author Comment

by:stvmph
ID: 39306074
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now