Publication Access List User Question (Anyone else?)

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
"dw" <dw@noSpam.com> wrote in message
news:205801c3886a$d6e1a990$a001280a@phx.gbl...
> 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
>
>
> >-----Original Message-----
> >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
> >
> >
> >
> >.
> >

kboske
Thu Oct 23 18:01:37 CDT 2003

Sounds like you are asking/answering two different questions.

In order to CREATE a publication, you will need to be a SysAdmin (see the
SQL Server Books Online topic: Replication Security: Roles).

In order to SUBSCRIBE to the publication, you will need to be part of the
Publication Access List, you don't need to be a sysAdmin.


In order to give each user a different set of data, you can use Dynamic
Filters. Take a look at the "Dynamic Filters" topic in the SQL Server
Books Online.

For example, you could filter a table on SUser_SName or Host_Name. Or, you
could generate seperate Publications, statically filtered for each
subscriber.

Kevin Boske
(kboske@microsoft.com)
SQL Server CE Team
Microsoft
----------------------------------------------------------------------------
----------------------------------------
Everything you need to know about SQL Server CE:

http://www.microsoft.com/sql/ce/techinfo/default.asp
----------------------------------------------------------------------------
----------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
----------------------------------------------------------------------------
----------------------------------------
--------------------
Reply-To: "JR" <a@b.com>
From: "JR" <a@b.com>
Subject: Publication Access List User Question (Anyone else?)
Date: Thu, 9 Oct 2003 12:09:05 -0700
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uSaeEkpjDHA.1764@tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: ip210.subnet74.gci-net.com 216.183.74.210
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:35583
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

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
"dw" <dw@noSpam.com> wrote in message
news:205801c3886a$d6e1a990$a001280a@phx.gbl...
> 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
>
>
> >-----Original Message-----
> >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
> >
> >
> >
> >.
> >