[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2404
  • Last Modified:

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

 
0
frrf
Asked:
frrf
1 Solution
 
frrfAuthor Commented:
I am using sql server 2000
0
 
Brian CroweCommented:
per BOL on SET IDENTITY_INSERT:

"Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner."
0
 
SweatCoderCommented:
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
rafranciscoCommented:
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.
0
 
frrfAuthor Commented:
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.

0
 
Anthony PerkinsCommented:
>>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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now