Solved

Cannot save a view

Posted on 2013-06-19
8
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
Resolve DNS query failed errors for Exchange
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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