Permission Issue

  • 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

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

    K. Brian Kelley

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

    K. Brian Kelley

  • 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

  • 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

    K. Brian Kelley

  • Very interesting. As to the original question, how is EM registered?

    Steve Jones

  • Thanks

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

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