Publication Access List User Question (Anyone else?)

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

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
 
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
([email protected])
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" <[email protected]>
From: "JR" <[email protected]>
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: <[email protected]>
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
 
Back
Top