Permission Issue

Question

Post reply

Permission Issue

Carla Stewart

Old Hand

Points: 353
More actions
January 2, 2002 at 1:45 pm

#57746

There is an issue coming up just recently that has both me and the DBA scratching our heads.

There is a view that has been created that displays every field in the table. It only displays from one table. The user has Select, Insert, Update, and Delete permissions to the view. When she tries to Insert a record she receives an error. "INSERT permission denied on object 'TABLENAME', database 'DATABASENAME', owner 'dbo'. We have checked and the view and table are both owned by dbo. Is there something I am missing

Viewing 6 posts - 1 through 5 (of 5 total)

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

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

K. Brian Kelley

SSC Guru

Points: 114532

January 2, 2002 at 2:11 pm

#421601

Are the view and table in the same database or different databases? If different, same or different owners (for the databases)?

K. Brian Kelley

bk@warpdrivedesign.org

http://qa.sqlservercentral.com/columnists/bkelley/

Edited by - bkelley on 01/02/2002 2:11:21 PM

K. Brian Kelley
@kbriankelley

Carla Stewart Old Hand Points: 353 More actions · Answer 2

The table and view are in the same database. I did find more information on this issue. The user was trying to insert a new record into the view by using SQL Enterprise Manager. Another Database Developer told me that when you edit by opening the view SQL actually writes the T-SQL statement directly referencing the table. Is there any proof to this???

Thanks for all your help

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 3

I created a table tableInsert and a view viewInsert to test if there was a difference. Opening the view and inserting, I saw the following in Profiler:

exec sp_executesql N'INSERT INTO "Sandbox".."tableInsert" ("InsertChar") VALUES (@P1)', N'@P1 varchar(10)', 'abc '

This insert is clearly on the table and not the view. Now, inserting through Query Analyzer into the view:

INSERT viewInsert VALUES ('abc')

This is against the view as expected. So it does look the other developer was correct... EM does an insert against the original table. Very odd.

K. Brian Kelley

bk@warpdrivedesign.org

http://qa.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley