December 7, 2005 at 10:13 am
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!
December 7, 2005 at 10:51 am
I don't think you can. Generally you must be the owner of the object (dbo) to run set commands against them.
December 8, 2005 at 12:22 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply