Revoke Remote failing

[former_SQLA_member1694871]

So we have four SQL Remote Users that we are attempting to revoke from the list of SQL Remote Users on our production database.

I would remove the users outright, but they will still be logging into the database internally in the office.

When I attempt to revoke each remote, I get the warning about subscriptions. I say I'm sure. Revoke the user's remote status.

Immediately I receive an error saying - The user user_name could not be modified in the database. Cannot revoke the permissions while user user_name is actively replicating transactions.

SQLCODE: -1080 SQLSTATE: 40001 SQL Statement: REVOKE REMOTE FROM user_name

I went into each users subscriptions and turned them off. And then removed them. I also did a remote reset on each user.

When I attempted to revoke the remote again, I still received the same error.

Am I missing something additional that needs to be deleted or changed in order to revoke these users from the SQL Remote Users list?

Any thoughts would be greatly appreciated!!

SQL Anywhere 12.0.1.3942

Thanks!

Jeff Gibson
Intercept Solutions
Nashville, TN

[Former Member]

Dropping the subscriptions would be the missing step I believe.

[VolkerBarth]

I went into each users subscriptions and turned them off. And then removed them.

I think that's meant with dropping, correct?

However, as to the docs (at tleast those for v16), a REVOKE REMOTE statement already does drop all subscriptions for the according user:

Automatic commit. Drops all subscriptions for the user.

Hm, I'm not sure whether this is different for v12. We have always simply dropped remotes by issuing REVOKE CONNECT from the according users, and that both revoked the REMOTE privilege and dropped all according subscriptions, even with v8 and before... However, those were always particular users that did not connect locally.

---

Besides that, is it possibly that SQL Remote has been running at the consolidated while you tried to revoke the remote privilege of those users, and/or were they connected locally at that time? (I don't know whether this should matter but I'd try when SQL Remote is not currently running and the users are not connected...)

[Former Member]

It would appear Volker is correct. You can get this error when either dropping or revoking a remote user. It will be thrown if dbremote was left running.

[regdomaratzki]

Is the user you are trying to revoke remote from connected to the database on another connection and using resources? If so, do you know what they are doing?

Is dbremote running at the time you try and REVOKE REMOTE?

Reg

[former_SQLA_member1694871]

Not at all Reg. We had kicked everyone out. Replication runs at the top of the hour at this site. We ended up even shutting down the service that fired off replication on the hour.

So I'm still stuck on these 4 users that can be revoked from a sqlremoteuser status back to a normal user status.

Thoughts?

[former_SQLA_member1694871]

We even stopped and then dropped the subscriptions manually on all of these remote users before attempting to remove them. So I'm still at a loss on where to go with this.

[former_SQLA_member1694871]

That's correct Volker. By remove I meant drop. But I was handling everything through the interface. Not the command line.

[VolkerBarth]

Puzzling. Just another wild guess: Would it be possible to drop those users completely and add them back as normal users?

(In order to preserve their passwords, you might do a reload first and preserve the according GRANT CONNECT IDENTIFIED BY ENCRYPTED... statements and re-apply them (and any other according GRANT statements) after the users have been dropped.)

[regdomaratzki]

Is it possible that you have events defined in the database that are holding resources preventing the REVOKE REMOTE?

This is the second issue you've posted that seemed to be about a mysterious connection holding locks/resources. What was the solution to the issue in August?

http://sqlanywhere-forum.sap.com/questions/27794/need-help-with-a-blocked-passthrough

Reg