Publication Access Lists

When you create a publication, Microsoft® SQL Server™ automatically creates a publication access list (PAL) for that publication. The PAL contains a list of logins granted access to publications. The logins included automatically in the PAL are the members in sysadmin and the current login.

The PAL functions similarly to a Microsoft Windows NT® access control list. When a user or replication agent attempts to log in to a Publisher, SQL Server first checks to see if the login is in the PAL. If you must further expand or restrict access to a publication, you can add or delete logins in the PAL using SQL Server Enterprise Manager or the sp_grant_publication_access and sp_revoke_publication_access stored procedures.

Note A replication agent’s login for the Publisher and Distributor must exist in the PAL before it can access the publication. The user login must also exist in the publication database or the database must allow guest users. If the Publisher and Distributor are not on the same server, the logins must exist at both the Publisher and the Distributor before it can be added to the PAL. Because the replication agent runs under SQL Server Agent, the account under which SQL Server Agent runs on a Windows NT platform must be in the PAL.

A snapshot, transactional, or merge publication may be secured with a PAL through SQL Server Enterprise Manager or programmatically.

If you have a large number of user logins to add to the PAL, consider making them all members of a single Windows NT group and then adding the Windows NT group to the PAL.

To grant or revoke access to a publication

To grant or revoke access to a transactional or snapshot publication

To grant or revoke access to a merge publication

See Also