Stored Proc to insert value into Identity Column

• dba_user

  Default port

  Points: 1458

  More actions

  December 7, 2005 at 10:13 am

  #166188

  I have a stored proc that inserts a value into an identity column. So, I need to set identity_insert ON.  The problem is that the login who is executing the stored proc only has public access to the database and execute permissions to the stored procedure.  How can I get this to work without giving the user more permissions to the database????  Help!

• ramses2nd

  Hall of Fame

  Points: 3720

  More actions

  December 7, 2005 at 10:51 am

  #608228

  I don't think you can.  Generally you must be the owner of the object (dbo) to run set commands against them.

• Jesper-244176

  SSCertifiable

  Points: 7142

  More actions

  December 8, 2005 at 12:22 am

  #608338

  Try this:

   

  create table test(id int identity(1,1), a int)

  go

  create proc insert_test

  (

   @id int,

   @a int

  )

  as

  begin

   set identity_insert test on

   insert into test (id, a) select @id, @a

   set identity_insert test off

  end

  go

  grant exec on insert_test to public

  go

  exec insert_test 1, 2

  insert into test (a) select 5

  drop proc insert_test

  go

  select * from test

  drop table test

  go