Post reply

Insert on linked server fails with "Select permission denied..."

  • December 15, 2004 at 12:00 pm

    #163451

    I'm trying to grant linked login insert (but not select, update or delete) permissions on a table on a linked server, with no success. I want the remote users to be able to insert into the table, but not have any other privledges.

    Here's what I've tried:

    I added the login on the linked server to the Public role. Both Insert and Selects from Query Analyzer work. Next (as a sanity test) I added the login to the db_datawriter and db_datareader roles. Again, selects and inserts work fine (these are all launched from the remote server). Next, I remove the login from db_datareader role but leave the login in the db_datawriter role. Now the select fails (as expected) but so does the insert!

    Error message is:

    Server: Msg 229, Level 14, State 5, Line 2

    SELECT permission denied on object 'ABC', database 'XYZ', owner 'dbo'.

    Is it possible that MSSQL does a select under the covers when it is inserting into a table on a linked server?

    Just for grins, I removed the login from all roles but Public, granted the login both select and insert table-level permissions. Insert and select work fine. Then I unchecked the select permission and select fails (as expected) but again so does the insert.

    Any ideas would be appreciated!

     

     

  • December 15, 2004 at 1:12 pm

    #534034

    Interesting problem. I don't have any firsthand knowledge on this subject but I'll be watching this thread to see what help others can provide. Good question.

  • December 15, 2004 at 1:14 pm

    #534036

    This makes perfect sense, micro$oft sense !  The specification for linked servers causes a select statement, so those permissions must exist.  This is one of the performance bottlenecks of linked servers is that an insert of one record will cause a select of all records.  We found this when trying to insert a row into an oracle table with several million rows was taking a long time, on tracing from the oracle side it was seen that sql server linked server insert was issuing a select * !

    From, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_oledbconn.asp ;

    SQL Server uses IOpenRowset:penRowset to open a rowset on the base table and calls IRowsetChange::InsertRow to insert new rows into the base rowset.

    The IOpenRowSet requires select permissions.

    This is also very inefficient.  For our inserts into oracle tables we created views with a where clause that always returned zero rows (where 1=0).  We then insert into the view.

     

  • December 15, 2004 at 1:23 pm

    #534037

    Thanks, Bob for your reply! I'll look into using an updatable view on my linked (MSSQL) server.

     

  • December 15, 2004 at 4:00 pm

    #534058

    It worked! Thanks!

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

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