Replication Woes continued

Well since I moved from merge to snapshot replication, i thought the problems were solved..But oh well

Here is the issue with snapshot replication...

Snapshot replication takes a snapshot of the database and replaces the existing database with the snapshot...as weird as it may seem, it does not carry the custom permissions set on the stored procedures and views to the new snapshot...

Hence, none of my stored procedures would work.

First instinct is to add the TSQL to the agent running the replication agent running the entire snapshot copy procedure...WRONG...Since the agent runs on the distribution machine, IAIDEV in my case...it runs the TSQL on the local machine and not on the remote machine...

Huh !! interesting...

So, I asked the google God and God said ..let there be command line...

hehehe...jokes apart...Now I am using the osql to run my script from the command line.

Its this handy little utility hidden inside the folder after folder...but once you get it working, it runs like a charm...

Here is what I am using finally -

I created a bat file with the following syntax to run the command on my machine
then used the windows scheduler to run the command 20 mins after the snapshot replication starts...this 20 odd mins, the remote machine would not have the correct permissions...but ah well..no one should be trying to get to my remote machine at 12 AM in the morning anyways...right ?
Besides, the wrong permissions are the lower permissions then what I give it eventually and so, there is no security hole here as well...
Anyways...back to the osql and bat file -

"[custom path]\Microsoft SQL Server\80\Tools\Binn\osql" -U username -P password -d master -i setPermissionsAfterReplications.sql