I spent some time troubleshoot permissions over a linked server recently before finding out the the cause of my error wasn’t permissions-related at all. I was attempting to perform an insert on a remote table, and was getting the following error:
Msg 7344, Level 16, State 1, Line 2
The OLE DB provider “SQLNCLI10” for linked server “RemoteServer” could not INSERT INTO table “[RemoteServer].[RemoteDB].[dbo].[IdentInsertTest]” because of column “ID”. The user did not have permission to write to the column.
After some time attempting to isolate the missing permissions, I realized that it was actually a disguised error message. I was trying to insert a value into an identity column, but rather than the standard error message I expected to see in that case, I got a generic “You don’t have permission” message, leading to some wasted time troubleshooting.
To recreate the issue, you can follow these steps:
-- Create a test table CREATE TABLE IdentInsertTest ( ID INT IDENTITY(1,1), SomeValue VARCHAR(10) ) -- This insert will succeed INSERT INTO IdentInsertTest (SomeValue) SELECT 'Some Value' -- Will fail with IDENTITY_INSERT error INSERT INTO IdentInsertTest (ID, SomeValue) SELECT 10, 'Some Value'
The second statement will fail with the standard error message:
Cannot insert explicit value for identity column in table ‘IdentInsertTest’ when IDENTITY_INSERT is set to OFF.
Now, connect to another server and set up a linked server to the other instance, and then try these statements again:
-- This remote insert will succeed INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (SomeValue) SELECT 'Some Value' -- Will fail with a permissions error INSERT INTO LinkedServer.RemoteDB.dbo.IdentInsertTest (ID, SomeValue) SELECT 10, 'Some Value'
If I’d realized what I was doing, it would have saved me some troubleshooting time! The moral here is that if your statement fails over a linked server, ensure your user account is set up correctly and then test it locally – you may get a more accurate error message!