0

I have just setup the ASE-ASE replication with a column as IDENTITY, when I tried to resume my DSI connection of my replication server, I'm hitting the error Message from server: Message: 10369, State 2, Severity 14 -- 'Permission denied. You must be either the database or the object owner or have sa_role to perform this operation. Further check from the dump queue, I could see some transaction is trying to do insert to the table with IDENTITY column, and probably it hits permission denied set identity_insert customer on insert into customer (id, name) values (1,'Ben')set identity_insert customer off. May I know how can I grant my replication user to be able to run the set identity_insert and set identity_update command?
Appreciate if you could help. Thanks in advance.

0

set identity_insert (and set identity_update) requires the user issuing the command to be the db owner, the object owner or have sa permissions.

Most DBAs will alias the maintuser as the database owner (dbo) in the replicate database.

While the DSI is down:

-- issued in the replicate database
exec sp_dropuser <maintuser>
go
exec sp_addalias <maintuser>,dbo
go

Now resume the DSI and the insert should go through.


If you're running newer versions of ASE (15.7, 16) you can enable granular permissions and then go through and grant individual permissions to the maintuser (eg, identity_insert and identity_update).

  • I got the result Warning: the specified account is currently active. Nothing changed when I run sp_dropuser <maintuser>. and when I run exec sp_addlias <maintuser>, dbo, it said that my maintuser is already a user in the replicate database. Any advise? My Sybase version is still ASE 15.5. – Tittus Mar 9 '18 at 1:20
  • Make sure the DSI is suspended (this will log the maintuser out of the RDB) before trying to drop the user. The sp_dropuser has to succeed before you can run sp_addalias. If you're using the maintuser for other purposes then you'll need to make sure there are no instances of said user actually in the RDB when you run sp_dropuser. – markp Mar 9 '18 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.