rails-sqlserver/activerecord-sqlserver-adapter — Issue #139: IDENTITY_INSERT on primary key column
Author: chadcf
Created: 2011-10-27 16:01:44+00:00
State: open

So I have a legacy db which has a rather poor design. In essence I have a users table, and an administrator table. If a user is an administrator, then their user id is inserted into the Administrator table. Which contains only one column, named ID, which is a primary key (but not an identity column).

When I try to save a value to this table, the adapter tries to run

SET IDENTITY_INSERT [Administrator] ON

This fails with an error about not finding object Administrator and that perhaps I do not have permission to do this. So I talk to my DBA. He tells me that the ID column is not an identity column, therefore does not need this option set. I tell him that I realize this, but it's attempting to be set and if I could just get the permissions life would be swell. He then tells me that it since it's not an identity column, there is no permission to give. I don't quite buy this, but that's where I'm at.

So I look at the code and notice that the sqlserver adapter considers something an identity column if it is indeed an identity column, OR it is a primary key column. Hence my problem, as ID is a primary key column. This seems like an issue as it's entirely possible to have a column that is a primary key but not an identity column, and there's no need to make sure you can insert an identity to a non identity column.

Is there a reason it does this? Is there any way to disable it other than to monkeypatch the adapter?