Are you using the same user in v12 and then new version when running dbremote?

Yes, I do.

Basically, the reload.sql file (because it is used for v12, as well) still does use the classic GRANT CONNECT, GRANT GROUP and GRANT REMOTE DBA statements for that user (and grant access to a few tables and views which that user has to access outside the DBREMOTE session). The script does not GRANT EXECUTE on the hook procedure but AFAIK, that's not required.

I'm not sure I like the idea of the same reload.sql being used for a v12 and v16/v17 database because of all the changes around permissions.

I'd suggest running the v16/v17 dbunload on the database and comparing how your reload.sql is defining permissions versus the way they are done in the reload.sql generated by dbunload.

It means you've found a poorly worded statement in the documentation. :)

It should say something along the lines of :

1) The user that executes this stored procedure must have the SYS_REPLICATION_ADMIN_ROLE system role.

Making sure this is true depends on whether the stored procedure is defined with the SQL SECURITY INVOKER or SQL SECURITY DEFINER (the default).

The hook is defined without a SQL SECURITY clause so it should be the default DEFINER (your 3rd option, methinks:)). The procedure is also listed as one of those which require particular privileges.

FWIW, when the remote dba user connects normally, she can not execute the hook procedure because of missing execute permissions. However, that should not matter for a DBREMOTE session.

FWIW, when I use a full DBA account to run DBREMOTE, it still does not call the hook procedure under v16/v17, just like the REMOTE DBA account. I'm puzzled.

And if I add another simple hook like that, it also does not get called - and it does not matter whether it's declared with SQL SECURITY DEFINER or INVOKER and whether it's owned by dbo or the remote dba user:

create procedure dbo.sp_hook_dbremote_begin() sql security invoker
begin
   message 'dbo.sp_hook_dbremote_begin() hook called' to log;
end;

If I add that simple hook procedure on v12, it surely gets called.

Here's a DAQ... there wouldn't be a problem with ownership, would there? I believe multiple hook procedures with identical names can coexist, and be called by different instances of dbmlsync started with different user ids matching the different hook owners. No, I have not done that myself but I'm helping a development process where I think it's happening (so far I've just closed my eyes and hoped it goes away :)

No, ownership does not seem to be the problem here, although I had considered that idea, too: I had created two hook procedures (one owned by dbo, one by the remote dba user named "RemUser") but neither one is called by DBREMOTE (not dbmlsync:)). (Of course it does matter when running the hook procedure interactively because then RemUser is not allowed to execute the dbo hook procedure without explicitly granted permission, as expected.)

AFAIK, the docs are rather quiet when it comes to hook procedures and ownership... But we've always used dbo ownership and never run into issues with that, both with SQL Remote and MobiLink.

BTW: What's a DAQ?

DAQ is dumb-a**-question, and you'll let me know if it was a DAQ or not :)

FWIW the symptom "does not execute a hook" sure feels like "does not see a hook" rather than "not authorized to execute a hook".

Hm, so now I have to consider whether my question on the meaning of DAQ was a DAQ itself? :)

FYI: Reg has got the according database, so I'm sure he will discover the cause of that symptom.

I'd buried a reply in the answer indicating that the source of the problem is that the stored procedure is owned by dbo. I'm currently checking with Those Who Know More Than Me About This Type Of Thing and will get back to you shortly. It might be next week though because The Person That Knows The Most is away today.

See my comment above (yes, there are too many here to not oversee some of them - I just noticed your reply from yesterday...):

I had created two hook procedures (one owned by dbo, one by the remote dba user named "RemUser") but neither one is called by DBREMOTE...

That made me think it's not about ownership...

I would not expect an sp_hook stored procedure owned by RemUser to be found or executed. The v17 docs state that :

Hook procedures can be created by any user with the MANAGE REPLICATION system privilege.

http://dcx.sap.com/index.html#sqla170/en/html/81afb4036ce210149cc3dbcb39a8a4d0.html

The v12 docs state that :

Hook procedures must be owned by a user with DBA authority.

http://dcx.sap.com/index.html#1201/en/mlclient/using-procs-customizing-ml-asa.html

It doesn't say that we look for procedures owned by users who at the time that dbremote is running have DBA authority (or the MANAGE REPLICATION system privilege).

I think you'll find that in v12, an sp_hook_dbremote stored procedure owned by RemUser will also not be found.

Just as a hint: The v17 link relates to MobiLink hook procedures, though those for SQL Remote are described similarily (except the poorly worded description, as we have discussed further up nested comment thread...:

http://dcx.sap.com/index.html#sqla170/en/html/95f074cf6ea11014bef4cc31fceaaf9a.html

So you basically say dbo must have the MANAGE REPLICATION privilege in v16/v17? (I have not yet checked whether this has been granted.)

EDIT: For v12, dbo has been granted DBA authority, just as the DBA user. So I would think that should mean when migrated to a compatibility role, dbo should be granted "SYS_AUTH_DBA_ROLE" which in turn has the mentioned "MANAGE REPLICATION" role and therefore should be able to specify hook procedures...

EDIT2: Now I have checked again: For v16, both dbo and DBA (the v12 users with DBA authority) are listed in v16 as users with the according privileges:

select * from (SELECT name from sysusers where name in ('dbo', 'dba')) SU cross apply sp_displayroles(SU.name, 'expand_down')
where role_name like '%replication%';

-- returns
name,role_name,parent_role_name,grant_type,role_level
DBA,MANAGE REPLICATION,SYS_AUTH_SA_ROLE,ADMIN,3
DBA,SYS_REPLICATION_ADMIN_ROLE,MANAGE ROLES,ADMIN ONLY,4
dbo,MANAGE REPLICATION,SYS_AUTH_SA_ROLE,ADMIN,3
dbo,SYS_REPLICATION_ADMIN_ROLE,MANAGE ROLES,ADMIN ONLY,4

So apparently (and as expected) the dbo user/role should have enough permissions to specify a hook procedure.