Manage Logins in the Publication Access List

This topic describes how to manage logins in the Publication Access List in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Access to a publication is controlled by the publication access list (PAL). Logins and groups can be added and removed from the PAL.

In This Topic

• Before you begin:

  Prerequisites

  Security

• To manage logins in the Publication Access List, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Prerequisites

• You must associate the SQL Server login with a database user in the publication database before you add the login to the PAL.

[Top]

Using SQL Server Management Studio

You use the publication access list (PAL) on the Publication Access List page of the Publication Properties - <Publication> dialog box to manage logins. For more information about how to access this dialog box, see View and Modify Publication Properties.

To manage logins in the PAL

1. On the Publication Access List page of the Publication Properties - <Publication> dialog box, use the Add, Remove, and Remove All buttons to add and remove logins and groups from the PAL. Do not remove distributor_admin from the PAL. This account is used by replication.

   Note

   If a remote Distributor is used, accounts in the PAL must be available at both the Publisher and the Distributor. The account must be either a domain account or a local account that is defined at both servers. The passwords that are associated with both logins must be the same.

2. Click OK.  

[Top]

Using Transact-SQL

To view groups and logins that belong to the PAL

• At the Publisher on the publication database, execute sp_help_publication_access. For @publication, specify the publication name. This displays information about the groups and logins in the PAL.

To add groups and logins to the PAL

• At the Publisher on the publication database, execute sp_grant_publication_access. For @publication, specify the publication name; and for @login, specify the name of the login or group that is being added.

To remove groups and logins from the PAL

• At the Publisher on the publication database, execute sp_revoke_publication_access. For @publication, specify the publication name; and for @login, specify the name of the login or group that is being removed.

[Top]

See Also

Concepts

Manage Logins in the Publication Access List

Replication Agent Security Model

Secure a Replication Topology

Secure the Publisher