Publication Access List User Question

  • Thread starter Thread starter Jason Randall
  • Start date Start date
J

Jason Randall

Hello,

I have a PPC application utilizing MS SQL 2000 replication and am able to
sync fine assuming my Publication Access List user is a member of the System
Administrator role for the SQL Server providing the Publication. However, I
don't want all my users that access this publication to be a member of the
system administrator role. Is their a setting of some kind that I can set
when creating my Publication? Also, one of the reasons it needs to be a
System Administrator is that when synchronization occurs a select query is
performed on the sysservers table in the master database which selects are
only allowed on that table by System Administrators. Any help would be
appreciated.

Thanks,

Jason Randall
(e-mail address removed)
 
Jason...

We are doing the same thing (a merge replication between
SqlCe and Sql2000. I have a Sql User login that is NOT
part of the Sql2000 SysAdmins and the replication works
fine. That said, we aren't replicating any data from the
system tables. Is there a reason you need to do that?
Could you create a View or something instead? I haven't
used Views and replication to know if that is even
possible. Maybe tell us more of what you are trying to
accomplish.
- dw
 
dw,

Thanks for your response. Are you able to create a publication through the
Enterprise Manager as a non sysadmin and only has access to the database you
want to replicate? When I attempt to do this I get the following error:

SQL Server Enterprise Manager could not retrieve information about the
Distributor or the database.

Error 229: SELECT permission denied on 'MSdistributiondbs',database 'msdb'
owner 'dbo'.

All I'm trying to do is pull information down from a simple table no views,
stored procs, etc. This does work when I login as a sysadmin user.
Eventually, each user that accesses the publication would get different sets
of data. We have over 1100 users so I don't want 1100 sysadmins. Would you
mind posting a script of what you use to create your publication that allows
non-sysadmins access to the publication.

Thanks,

Jason
 
Back
Top