\nYesterday I was working on implementing transactional replication with the goal of limiting the permissions each replication account ran under. <\/span>I created three separate domain accounts for the snapshot, log reader and distribution agents. <\/span>These accounts had no other permissions before I began:<\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>I created logins on the publisher and distributor (in this case, the same SQL Server instance) and I added the snapshot and log reader agent accounts to the db_owner role of the distribution and publisher databases.<\/font> <\/font><\/span><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>This was a push subscription, so I also added the distribution agent to the db_owner role for the distribution database, but I did not <\/em>grant it access to the publication database. <\/span>I did make the distribution agent a member of db_owner for the subscription database (which was located on a separate server and default instance).<\/font><\/font>\n<\/p>\n \n·<\/font><\/font> <\/font><\/font><\/span><\/span><\/span>I gave the snapshot agent “write” permissions and the distribution agent “read” permissions to the snapshot share.<\/font><\/font>\n<\/p>\n \nBy the way, all this talk of db_owner makes it sound like I wasn’t limiting permissions all that much; however this fixed database role membership is indeed a minimum requirement in this implementation. <\/span>It’s also typically more restrained then what I’ve seen out in the field. Usually I’ll see the use of domain accounts with sysadmin used to manage everything<\/em> in the replication topology. <\/span>I don’t usually see a separate set of accounts configured for each agent role, nor do I see them set up for each unique topology (for very large environments, the administrative overhead may not make this a practical choice – but that’s another discussion altogether).<\/font><\/font>\n<\/p>\n \nI did leave out one step though – and I’ll get to that in a moment. <\/span>After applying the permissions I described, I set up the new publication and new subscription, and the data flowed correctly with no issues and no sysadmin permissions required.<\/font><\/font>\n<\/p>\n \nThe step I specifically left out was the adding of the distribution agent to the Publication Access List (PAL). <\/span>According to Books Online, “Access to a publication is controlled by the publication access list (PAL)<\/em>.” <\/span>Also according to Books Online, the distribution agent for a push subscription must “Be a member of the PAL<\/em>.” <\/span>I wondered why? And if this is such a key area – why don’t we hear much discussion of the PAL? <\/span>If you search the replication forums, you’ll find very few questions about it (searching today, I found 26 loosely related threads). <\/span>Either this means that most shops use high privilege accounts and haven’t pushed further to find out the role of PAL – or the PAL role isn’t entirely what it seems to be (as its described, it seems to suggest that the distribution agent account needs membership in order to synchronize).<\/font><\/font>\n<\/p>\n \nNow if it must<\/em> be a member, why was transaction replication working properly (rows were moving fine from publisher to distributor and distributor to subscriber).<\/font> <\/font><\/span><\/font>\n<\/p>\n \nMy first assumption was that I missed something or that somehow the distribution agent account was getting implied permissions either through group membership.<\/font><\/font>\n<\/p>\n \nThe first thing I validated was the current PAL list of accounts (looking explicitly for my distribution agent account – called SQLskills\\SQLskillsDistAGT). <\/span>Looking at the PAL, this account had NOT been explicitly granted membership somehow through other activities:<\/font><\/font>\n<\/p>\n \n \nPerhaps SQLskills\\SQLskillsDistAGT was gaining access through group membership? <\/span>Seemed unlikely to me, but I checked nonetheless by using EXECUTE AS LOGIN and querying the sys.login_token to see the groups associated with that account:<\/font><\/font>\n<\/p>\n \n \nI didn’t see any connections or group memberships that would map to the PAL.<\/font> <\/font><\/span><\/font>\n<\/p>\n \nMy next thought was to examine the SQL Server Agent job and ensure it really was running under the context of SQLskills\\SQLskillsDistAGT. The SQL Server Agent Job for the distribution agent was owned by the Administrator account, but<\/em> the job step itself was running as the SQLskills\\SQLskillsDistAGT proxy:<\/font><\/font>\n<\/p>\n \n \nThe proxy maps to a security credential, which in this case was my SQLskills\\SQLskillsDistAGT account. <\/span>I validated the mapping by querying sys.credentials (checking the credential_identity column):<\/font><\/font>\n<\/p>\n \n \nSo the mapping was what I expected.<\/font> <\/font><\/span><\/font>\n<\/p>\n \nBut was the job really connected as that account? <\/span>I ran a few test transactions at the publisher and again confirmed that rows were flowing to the subscriber. <\/span>I then queried sys.dm_exec_sessions for the distribution agent session, checking the login name and running a few times to ensure it was incrementing the logical reads:<\/font><\/font>\n<\/p>\n \n \nLogical reads were incrementing and the job was indeed running under the account.<\/font><\/font>\n<\/p>\n \nSo where are we? <\/span>Basically, I could find no connection whatsoever between the PAL membership and my distribution agent account.<\/font> <\/font><\/span><\/font>\n<\/p>\n \nSo because I wanted to be absolutely sure (and because this was a test environment) I removed all <\/em>accounts from the PAL (including “sa”). <\/span>I did so one-by-one, testing to see if it broke replication. <\/span>And guess what? <\/span>Replication just kept on working. <\/span>I even restarted the agents to see if it would initiate some kind of challenge-response, and it did not.<\/font> <\/font><\/span><\/font>\n<\/p>\n \nSo is PAL access required? <\/span>And if so, what is the boundary of that requirement?<\/font><\/font>\n<\/p>\n \nI logged off of my Administrator account and logged in to the publisher\/distributor SQL Server instance as the SQLskills\\SQLskillsDistAGT. <\/span>I then opened up SSMS and looked to see if I could view the publication:<\/font><\/font>\n<\/p>\n \n \nNo publications to be seen<\/font><\/em>, even though this account is actually responsible for running the distribution agent and is doing so successfully.<\/font><\/font>\n<\/p>\n \nI then jumped back on my Administrator account and first added SQLskills\\SQLskillsDistAGT to the public role of the publication database (required in order to be seen in PAL) and then I added SQLskills\\SQLskillsDistAGT to the PAL:<\/font><\/font>\n<\/p>\n![\"clip_image002\"](\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/when-is-the-publication-access-list-requ\/659deca1\/clip_image002.png\" "\\"clip_image002\\"")
<\/a><\/span>\n<\/p>\n<\/a><\/span>\n<\/p>\n<\/a><\/span>\n<\/p>\n<\/a><\/span>\n<\/p>\n<\/a><\/span>\n<\/p>\n<\/a><\/span>\n<\/p>\n