Insert on linked server fails with "Select permission denied..."
-
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!
-
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.
-
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.
-
Thanks, Bob for your reply! I'll look into using an updatable view on my linked (MSSQL) server.
-
It worked! Thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply