Replicating Database Security

When setting up an occasionally connected database to work off-line, I had a great thrill the first time the replication snapshot actually made it to the client with all tables, data, and stored procedures intact. In fact, after days of frustration, I was so happy I went home for the day determined to put the entire replication experience (unpleasant) out of my mind for the evening!

Fast-forward to our iteration review demo the next day: "Hey, let's see if the application works off-line, now that we have replication working." "Okay, here goes…" Of course, we immediately got an error telling us that the user could not open the database specified in the login: our new off-line client was broken.

After spending the rest of the morning sobbing in the corner, I thought I'd look into the problem a little bit. It turns out that the DisconnectedClient_Role database role and associated permissions had not replicated to the client and Domain Users did not have access to the database. Only tables, procedures, views, and functions can be replicated.

My first resource, Google, failed to return anything useful so, I went to my second resort/newsgroup aggregator, groups.google.com. A quick search on microsoft.public.sqlserver.replication turned up the solution; sp_addScriptExec. This is basically a carte blanche post-initialization script deployment tool. I took my role creation script and stored procedure permission script and saved them in a file where both the publisher and subscriber could see and access it. Then, I executed the sp_addScriptExec against the publisher database like this:

sp_addScriptExec
@publication='DisconnetedClient_replicated'
, @scriptFile = '\\snaphot_storage_location\DisconnectedClient_role.sql'
, @skipError = 1


It immediately sent the role and permission changes down to all active subscribers and any subscriptions that were created subsequently received the permissions as part of the snapshot -- we now had security. This time, I actually logged into a client and verified that the off-line client could access the local copy of the database. Success!