Problem with permission error with sp_makewebtask

[AndyH1]

Iv'e been try to get an automated web page created using the web assistant on my MainArticles database whenever I change data in a table one of the tables

I created a trigger on the table which runs the following code

CREATE PROCEDURE usp_createMainArticlesPage

AS

SET NOCOUNT ON

EXECUTE sp_makewebtask @outputfile = 'C:\Inetpub\

http://Wwwroot\mainarticlesincludetest.inc',

@query = 'SELECT TOP 1 txtImageName, intImageWidth, intImageHeight, txtAltTag, txtTitle, dbo.TidyUpSpaces(dbo.InsertEmaillinks(dbo.InsertHyperlinks(dbo.fnGetFirst2Paragraphs(txtMainText)))), ID FROM tblMain ORDER BY dteDatePosted DESC, ID DESC
SELECT TOP 4 ID, txtTitle FROM tblMain WHERE ID NOT IN (SELECT TOP 1 ID FROM tblMain ORDER BY dteDatePosted DESC, ID DESC) ORDER BY dteDatePosted DESC, ID DESC', @templatefile = 'C:\\Inetpub\

http://Wwwroot\MAINARTICLE.TPL',

@dbname = 'MainArticlesdb', @rowcnt = 3
IF @@ERROR <> 0
BEGIN
RETURN @@ERROR
END
RETURN
GO

CREATE TRIGGER tr_create_frontpage_insertFile ON dbo.tblMain
FOR INSERT, UPDATE, DELETE
AS
EXECUTE usp_createMainArticlesPage

But whenever it runs I get the following error:

EXECUTE permission denied on object 'sp_makewebtask', database 'master', owner 'dbo'.
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_makewebtask', database 'master', owner 'dbo'.

I have a local development version of this code running on my laptop which uses win 2003 server with MS SQL 2000 (SP3) and there is no problem it runs fine. On the main server (which is windows 2000 server with MS SQL 2000 (SP3) I get this error. I checked my local version and sp_makewebtask does not have execute permission for public or guest so it doesn't look as if this is the problem.

My sql server is set to use sql authentication is server properties, the remote server is set to windows authentication. Granting public execute permission on sp_makewebtask on the remote server gives the following (different error)
[Microsoft][ODBC SQL Server Driver][SQL Server]Only members of the sysadmin role can execute this stored procedure

I am not allowed to change the authenication method on the remote server. Anyone suggest a workaround?

Thanks
Andy

 

[AndyH1]

Because the server properties is set to use windows authentication I assumed I use the windows login company_admin which we log into the server with and grant it execute on sp_makewebtask in the master database

Tried
USE master
GRANT EXEC ON sp_makewebtask TO company_admin
GRANT EXEC ON xp_makewebtask TO company_admin

where company_admin is the windows login name we log in with but it says user not recognised when run the SQL statement in query analyser (I assume because this user name is not in master database users list and is also not in the logins folder. Also tried

GRANT EXEC ON sp_makewebtask TO COMPANY-BITA\company_admin
GRANT EXEC ON xp_makewebtask TO COMPANY-BITA\company_admin

and
GRANT EXEC ON sp_makewebtask TO 'COMPANY-BITA\company_admin'
GRANT EXEC ON xp_makewebtask TO 'COMPANY-BITA\company_admin'

but both give syntax errors, so I'm unclear what user name I need to give execute permissions.