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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy