CREATE USER IN MSSQL STORED PROCEDURE

Can you create a user and grant privileges using CREATE USER and GRANT in a stored procedure using variables from a web page? I have code in my stored procedure to insert into the USER and DB tables but when I try to connect from my web page I receive an access denied error. However, when I create a new user via the MYSQL Admin gui I have no problem connecting from my web page. MYSQL 5, PHP 5, APACHE 2, Windows xp. My store procedure code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_add_client`(IN parm_name varchar(16))
BEGIN
     DECLARE temp_password varchar(45);
     set temp_password = concat('test',right(rand(),5));
     start transaction;
     /*insert mymathhelper.clients (name,password,chg_password)
         VALUES(parm_name,
                temp_password,
                1);*/
     insert mysql.user
     (  Host,
        User,
        Password,
        Select_priv,
        Execute_priv,
        ssl_cipher,
        x509_issuer,
        x509_subject )
     values
           ('localhost',
           parm_name,
           temp_password,
           'Y',
           'Y',
           'null',
           'null',
           'null'
           );
     insert mysql.db
     (  Host,
        Db,
        User,
        Select_priv,
        Execute_priv)
     values
           ('localhost',
           'mymathhelper',
           parm_name,
           'Y',
           'Y');

     commit;
END
MathHelperAsked:
Who is Participating?
 
mankowitzCommented:
does your website access mysql with the same credentials as the local machine? When you log on as admin, you may be using the superuser account, while the web user has a more limited account.
0
 
nitinmehtaCommented:
are you sure your code is inserting the data in right tables?
0
 
nitinmehtaCommented:
I think, it should be:

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_add_client`(IN parm_name varchar(16))
BEGIN
     DECLARE temp_password varchar(45);
     set temp_password = concat('test',right(rand(),5));
     start transaction;
     /*insert mymathhelper.clients (name,password,chg_password)
         VALUES(parm_name,
                temp_password,
                1);*/
     insert INTO mysql.user
     (  Host,
        User,
        Password,
        Select_priv,
        Execute_priv,
        ssl_cipher,
        x509_issuer,
        x509_subject )
     values
           ('localhost',
           parm_name,
           temp_password,
           'Y',
           'Y',
           'null',
           'null',
           'null'
           );
     insert INTO mysql.db
     (  Host,
        Db,
        User,
        Select_priv,
        Execute_priv)
     values
           ('localhost',
           'mymathhelper',
           parm_name,
           'Y',
           'Y');

     commit;
END
0
 
MathHelperAuthor Commented:
The procedure is call using by a guest account that has execute privileges. The connection is made using the parms ("localhost", "guest", "guest password", "database name"). The user and grants table rows are being created. I'm concerned that since I'm manually inserting these tables that I'm missing some info that is created by using "create user".  I'm testing all of this from a single laptop. PHP connects via Apache as localhost.
0
 
nitinmehtaCommented:
password field's value cannot be "temp_password", it should be password('temp_password').

hope that helps...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.