2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Julio Carvalho
  • 151
  • 2
  • 4
  • 4
    Why do you want to enter an explicit value into the id column? If you want to do so, then why create it with an identity type? – Eli Sep 12 '17 at 18:50
  • 1
    Looks like a permissions issue. Make sure that login / user has proper permissions for that table, a quick check would be to make that user dbowner. – Joe C Sep 12 '17 at 18:53
  • Only an admin should ever be setting identity property to on. There is a huge potential for data integrity problems when doing this. It is not a recommended practice. So why are you trying to set the id value? – HLGEM Sep 12 '17 at 18:59
  • I supporting a legacy project. It came this way – Julio Carvalho Sep 12 '17 at 20:03

2 Answers2

3

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

cskwg
  • 790
  • 6
  • 13
  • This is a terrible idea since it’d have to be done to any user attempting this operation. It also would allow them to alter other objects as you currently have it written. It’s like hitting a nail with a 500lb bomb instead of a hammer – S3S Jun 16 '19 at 14:14
  • If you're giving all your users rights to the database, you already have problems. We are always using a single technical user per background service. Only this 'user' has rights to change the data. – cskwg Jun 17 '19 at 05:22
  • Great idea. Now you can’t audit who changed what. – S3S Jun 17 '19 at 11:08
  • our application does auditing. – cskwg Jun 17 '19 at 13:40
2

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')

S3S
  • 24,809
  • 5
  • 26
  • 45