We have a table with an identity field
Occasionaly data is archived out to a seperate database (not my design but stuck with it for now). If an archived record is re-instated we need to insert back into the original table specifying the identity field (so it gets the same ID as last time)
the user performing the task is a bog standard user i.e. Not db_owner or sysadmin.
I have created a stored proc to perform the reinstate and tried to grant execute permissions as db_owner using my sysadmin account but get the message
Grantor does not have GRANT permission.
I have granted myself execute permissions and made myself db_owner but still get the same error.
I then tried to specify the GRANT to use my login (sysadmin user) , this went through but I still get the error when executying the stored proc as the standard user
The current user is not the database or object owner of table 'DB.dbo.Table1'. Cannot perform SET operation.
Anyone shed any light or am i going to have to rewrite the system to avoid the identity field??
MCDBA, MCAD, MCITP, MCTS
View Complete Post