Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

MySQL Create Procedure - Access Denied

I've come across a few similar questions here, but have yet to figure out the answer.

I have a MySQL database that I connect to remotely (using Navicat) as the root user.

I'm trying to create a procedure using the following code (sent to me by a developer);

CREATE DEFINER=`root`@`localhost` PROCEDURE `webservice`.`Qry_WatchList`(_ProductId INT, _WebServiceId INT)
BEGIN
select WatchListId, WSProductId, Title, Description, Currency, Price
from tblwatchlist wl where wl.`ProductId`=_ProductId
and wl.`WebServiceId`=_WebServiceId ;
END $$

I'm getting 'access denied' messages for user root@ - and for life of me I can't figure out why.  I'm able to create databases, tables etc without any issues at all - but this simply will not execute at all.

I also tried;

CREATE PROCEDURE Qry_WatchList(_ProductId INT, _WebServiceId INT)
select WatchListId, WSProductId, Title, Description, Currency, Price
from tblwatchlist wl where wl.ProductId=_ProductId and wl.WebServiceId=_WebServiceId;

With the same result.

Any pointers very much appreciated!
0
Maxafi
Asked:
Maxafi
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the DEFINER is without the host name..
CREATE DEFINER=`root` PROCEDURE `webservice`.`Qry_WatchList`(_ProductId INT, _WebServiceId INT)
BEGIN
 ...

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now