Good morning,

I would like to use the sp_dropmergepullsubscription procedure to drop
(and later cleanup) an anonymous pull subscription.

My SQL Server (containing the subscription) is local, also IIS 5.0 and
the ASP.NET application.

I have added the ASPNET user to the db and gave him the db_owner role.

Now, when I run the sp from ASP.NET I get the following error:

Only members of the sysadmin fixed server role and
'MACHINE\Administrator' can drop the pull subscription to the
publication 'SERVER:Database:Publication'.

Does anyone have an idea how get around this problem?

Many thanks in advance.
Daniel.

Hi Paul,

Do you mean in the web.config file? I have no entry <identity
impersonate=... /> in my web.config file.

Rgds.
Daniel.

Please can you check the value of <identity impersonate=... /> - it may be
that the system is not using the ASPNET account.
Rgds,
Paul Ibison

Actually, you're quite right - I just tested it and I fully agree that BOL
needs updating to reflect this behaviour.

This is from the stored procedure code:

** Only members of the sysadmin group and the creator of the
distribution
** agent can drop a pull subscription successfully. This behavior
matches
** the behavior of the sysjobs_view. DBO of the subscriber database,
** sysadmins (owner is undefined) can drop a subscription if the
owner_sid
** is null.

So, if the user is also the job owner (merge agent, not 'distribution agent'
as mentioned in the comment) it is possible to run the procedure - I just
tested this and it works fine. Not too sure what causes the owner of a job
to be null in sysjobs, but this is when being DBO is sufficient.

HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

This is on the server where you have the merge job - in your case the
subscriber as you're using pull.
The procedure to run is sp_update_job to change the job's owner to ASPNET,
which should allow you to run sp_dropmergepullsubscription
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul,

many thanks for your answer. There are some things I do not quite
unterdstand.

- How do I make ASPNET the owner of a merge agent job?
- Do you mean the merge agent job on the Distributor machine?
- Our ASP.NET application runs locally on a computer with IIS 5.0 and
MSDE. We need to clear a local anonymous pull subscription using sp.
The distributor is a remote Win2k3 server with IIS 6.0 and SQL Server
2000 sp3a. The server does not have a user ASPNET, but NETWORK SERVICE,
so which one do I need?

Many thanks and regards.
Daniel

Daniel,
something like this should do:

select merge_jobid
from MSmerge_replinfo rep inner join sysmergesubscriptions sub
on rep.repid = sub.subid
where sub.publication = 'NorthwindD'

HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul,

we create our subscriptions with the SQLMergeClass. As far as I know,
we do not know a job id or job name, when we create the subscription,
so we will not be able to use the stored proc sp_update_job because
this sp explicitely needs a job name or job id. Or is there a
possibility to get a job id from an existing pull subscription?

Many thanks and regards.
Daniel.

Paul,

many thanks for your answer. It leaded me to the solution:

I noticed, that using your SELECT query on a subscrption created by
SQLMergeClass, I do get 2 records (for one subscription) with always
NULL as the merge_jobid and ASPNET as login name. When I create a
subscription manually with the Enterprise Manager, your query results
in one record with a merge_jobid and Administrator as login name. So I
tried to use my sp_dropmergepullsubscription on the subscription
created by SQLMergeClass and in fact it succeeded (because the login
name was ASPNET).

I did not try anymore but I think sp_dropmergepullsubscription will
still not succeed on my manually created subscription without changing
the owner.

Many thanks and kindest regards.
Daniel.