I've setup transactional replication my two servers, server1(publisher) &
server2(subscriber). If I logon to server2 using the sa username, I can
select and update the replicated data just fine. But if I try to logon using
a different name, I can select data from the replicated table, but I can't
perform updates, deletes, or inserts. I get the error:
Server: Msg 7415, Level 16, State 1, Line 4
Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must access
this provider through a linked server.
I checked all my login mappings and it looks like they're okay.
1. The username I'm using is part of the PAL list for the publication.
2. The username has update, insert, delete privileges.
3. The username exists on both the publisher and subscriber.
4. A remote server entry is setup for server1 & server2 (on both the
publisher and subscriber), with the RPC box checked.
If I try and setup a linked server like the error asks, it tells me that the
server already exists, even though I don't have any linked servers in the
item list. What else do I need to do? I don't want all my subscriber users
to use the sa logins to update tables.