Using views first time around

Question

Post reply

Using views first time around

Thomas Wareham

SSC Veteran

Points: 219
More actions
September 12, 2007 at 1:51 am

#174644

Hi, I'm new to views and am trying to create and use one for the first time (just as an exercise, on a dummy table).
The code is very basic such as:
CREATE VIEW newview
AS SELECT *
FROM Courses
GO
CREATE LOGIN NewUser WITH PASSWORD = 'password'
USE master
CREATE USER NewUser FOR LOGIN NewUser
GRANT SELECT ON newview to NewUser
GO
Okay so as far as I was aware that was all there was to it, after that you'd login as NewUser (which I've tried) and you'd be presented with the view, in my case I login with NewUser and yeah there is nothing there, no tables, no views, consequently trying to execute the following while logged in as NewUser does not work:
SELECT * FROM newview
GO
"Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'newview', database 'master', schema 'dbo'."
So what am I missing, did I have to give permissions to the actual database or something? if so the textbook I'm using failed to mention any of this!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

You shouldn't need to grant perms to the table.

Here is the code I ran and it worked 100%

CREATE

TABLE Courses

(ID INT)

CREATE

VIEW newview

AS

SELECT *

FROM

Courses

GO

CREATE

LOGIN NewUser WITH PASSWORD = 'password'

USE

master

CREATE

USER NewUser FOR LOGIN NewUser

GRANT

SELECT ON newview to NewUser

GO

--LOGIN AS NewUser

SELECT

*

fROM

newview

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 2

yeah thats the exact code I have so you didnt really help there. But how do you mean yours worked, like when you login as the newuser what happens, do you see the view under the master database or what?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi There,

What I mean is that I can actually run a select on the view.

I think if you want to see it under the Database you need to grant the following permission

GRANT

VIEW DEFINITION ON <OBJECT> TO <USER>

GO

THanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life