frrf
asked on
set identity_insert on in stored procedure
Hi,
in the stored procedure, I have "set identity_insert TableName on"
then I grant the exec of this stored procedure to userA
but when I run the stored procedure using userA's login
I got an error saying the set is not permitted for userA on table "TableName"
the userA is not the database owner, its roles are public, db_datareader, db_datawriter.
Can you tell me why I got the error? is there any special reason for the set identity_insert?
thanks
in the stored procedure, I have "set identity_insert TableName on"
then I grant the exec of this stored procedure to userA
but when I run the stored procedure using userA's login
I got an error saying the set is not permitted for userA on table "TableName"
the userA is not the database owner, its roles are public, db_datareader, db_datawriter.
Can you tell me why I got the error? is there any special reason for the set identity_insert?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're asking what is the special reason for set identity insert? i'm confused by this, because why are you trying to run this command if you don't know what it does. . .?
set identity insert is needed when copying data from one table to another, and you want the Identity field from the source table to populate the identity field in the destination table.
set identity insert is needed when copying data from one table to another, and you want the Identity field from the source table to populate the identity field in the destination table.
SET IDENTITY_INSERT's execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner. Since userA is neither of these, you get the error.
You set IDENTITY_INSERT to ON if you want to insert into a table with identity field and you specify the value you want inserted to that field. Try removing this SET and see if your sp still works as designed.
You set IDENTITY_INSERT to ON if you want to insert into a table with identity field and you specify the value you want inserted to that field. Try removing this SET and see if your sp still works as designed.
ASKER
ok,
as I understand, if you grant execution to userA for the stored procedure,
userA should be able to run the stored procedure no matter what in the stored procedure.
but it seems not true since the "set identity_insert..." is an exception.
so anyway to overwrite the default permission and grant it to useA only in this stored procedure?
@@SweatCoder
I know what I want to do, sorry didn't phase my question well.
as I understand, if you grant execution to userA for the stored procedure,
userA should be able to run the stored procedure no matter what in the stored procedure.
but it seems not true since the "set identity_insert..." is an exception.
so anyway to overwrite the default permission and grant it to useA only in this stored procedure?
@@SweatCoder
I know what I want to do, sorry didn't phase my question well.
>>userA should be able to run the stored procedure no matter what in the stored procedure.<<
Nope.
>>I know what I want to do, sorry didn't phase my question well.<<
You need to rethink it.
Nope.
>>I know what I want to do, sorry didn't phase my question well.<<
You need to rethink it.
ASKER