Solved

Cannot save a view

Posted on 2013-06-19
8
305 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 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