Rights to create a view

Question

Rights to create a view

ChrisLee

SSC-Addicted

Points: 483
More actions
June 24, 2004 at 12:30 pm

#102078

Hi,
I setup user with db_public, db_owner, db_datareader and db_datawriter rights. He sill could not create a view from SQL Analyzer, or created a view under SQL Enterprise but could not save it. Did I miss anything? Thanks.
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

If a user has db_owner permission, you shouldn't have any problems. Can you post the SQL and the error message. Are you trying to access a table in another database ?

ChrisLee SSC-Addicted Points: 483 More actions · Answer 2

Hi SA24,

Thanks.

That's what I thought. No, he was not trying create on different database.

Server: Msg 262, Level 14, State 1, Procedure v_xxxx, Line 3

CREATE VIEW permission denied in database 'The database that setup for user'.

He could create the view under SQL Enterprise database/view. Howver, he could not save the view.

Chris

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

Can you try creating another user and grant him db_owner permission and try with that user.

can you verify the registration property my right clicking your SQL server and verify that the user which you have granted permisisons is the same you are connecting to.

ChrisLee SSC-Addicted Points: 483 More actions · Answer 4

I used my own workstation. Kick off SQL Analyzer from SQL Enterprise (SA registered/login), I could create the view without any proble. If I diconnect/connect with user's id/passwrod, then I got "...permission denied..." error message. I even asked him to un-register and re-register the SQL before creating the view from his own workstation; but no luck.

Chris

sa24 SSCrazy Eights Points: 8027 More actions · Answer 5

can you run sp_helpuser from query analyzer and see what permissions the user has ?

Also, if you restored the database from a backup, you might want to run

sp_change_users-login 'auto_fix',username

ChrisLee SSC-Addicted Points: 483 More actions · Answer 6

matuschakb_rw	db_owner	matuschakb_rw	master	12	0xF6C40F68DEE7EC4BA3BF14B4CF61D29E
matuschakb_rw	db_datareader	matuschakb_rw	master	12	0xF6C40F68DEE7EC4BA3BF14B4CF61D29E
matuschakb_rw	db_datawriter	matuschakb_rw	master	12	0xF6C40F68DEE7EC4BA3BF14B4CF61D29E

user needs to create a view in database other than master.

Ole Jonny Brænne Old Hand Points: 378 More actions · Answer 7

In SQL Server 2000, create view rights is granted only to db_owner and db_ddladmin database roles.

Why are you granting db_public, db_datareader, and db_datawriter in addition to db_owner to the user you created? The permissions of database role db_owner span all of the other fixed database roles.

ChrisLee SSC-Addicted Points: 483 More actions · Answer 8

Except db_owner, all other rights were granted to user by our DBA. I was told to grant user rights to create view. db_owner was granted to user much later.

Thanks, Chris

sa24 SSCrazy Eights Points: 8027 More actions · Answer 9

sa24

SSCrazy Eights

Points: 8027

June 29, 2004 at 10:20 am

#512486

Did this get resolved ?

ChrisLee SSC-Addicted Points: 483 More actions · Answer 10

No. Our DBA told me he opened an ticket with MS. I will find out the status later.