SQL Server IDENTITY_INSERT Cannot find the object "TableName"

Question

When I run in java netbeans:

executeUpdate("INSERT INTO TableName (id, name) VALUES (1, 'Name1')")

I get the error:

Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF

If I run:

executeUpdate("SET IDENTITY_INSERT TableName ON;INSERT INTO TableName (id,name) VALUES (1,'Name1');SET IDENTITY_INSERT TableName OFF;") 

I get this error:

Cannot find the object "TableName" because it does not exist or you do not have permissions.

Why does this happen and how can I solve this?

Answer 1

Just let the IDENTITY property do what it is supposed to an only pass in the name. No need explicitly attempt to pass in an ID unless you are trying to associate specific ID's with names, in which you'd have to keep up with the values you have used and haven't used, and IDENTITY would then be sort of useless. I'd just add a unique constraint on the ID column in this case.

INSERT INTO TableName (name) VALUES ('Name1')

Answer 2

It is sufficient to give ALTER rights.

GRANT ALTER TO USER

https://sqlblog.org/2010/12/14/what-permissions-are-required-for-set-identity_insert-on