Windows Azure Mobile Services - Accessing a Stored Procedure from a different Schema

  • Article

I needed to access a stored procedure in the same database but for a different schema and call it from a server side script in Windows Azure Mobile Services, however I was getting this error: Error occurred executing query:  “Error: [Microsoft][SQL Server Native Client 10.0][SQL Server]The EXECUTE permission was denied on the object 'Test', database 'jsanders_db', schema 'jsandersauthdemo'.”.  This made sense to me because I was calling across Schemas.  The script looked like this:

 function read( query, user, request )
{

    mssql.query( "exec GetSomeStuff ?", ["%eff%"], {
        success: function ( results )
        {
            console.log( results );
        }
    } );

    mssql.query( "exec jsandersauthdemo.Test", null, {
        success: function ( results )
        {
            console.log( results );
        }
    } );

    request.execute();

}

 

The first stored procedure ‘GetSomeStuff’ was in my database JeffDemo and succeeded but the second was in jsandersauthdemo and failed with the above error.

image

The solution was to grant the appropriate permissions to allow me to call ‘jsandersauthdemo.Test’.  Apparently the calling user was different depending on the scripting environment in Mobile Services.

To see what users were accessing the schemas I first clicked on my database in the Azure management dashboard and installed the tools from there (steps 1-3 following that link):

image 

Hitting the Windows Key and start typing SQL I found the SQL tools opened it up and connected to the SQL Azure db.  You can find the name of your server here:

image

Connect to the server and go down to the schemas folder in the SQL Server Object Explorer.  The two I am concerned with are JeffDemo (where I am calling from) and jsandersauthdemo (where the failing stored procedure is).  Right clicking on ‘JeffDemo’ and choosing ‘View Permissions’ I can see what user has permissions to access that schema!  I will use that user information and grant permissions to the stored procedure for that user.

GRANT CONTROL

    ON SCHEMA::[JeffDemo] TO [iMCrzkYNNaLoginUser]

    AS [dbo];

Under Programmability/Stored Procedures I found and right clicked on my procedure I was failing on and chose ‘View Permissions’ and then added the grant permissions for the User from JeffDemo:

 /*

No permissions were found for [jsandersauthdemo].[Test] or all existing permissions are open in other windows

*/
GRANT CONTROL
    ON [jsandersauthdemo].[Test] TO [iMCrzkYNNaLoginUser]
    AS [dbo];

Simply hit the ‘Update’ icon at the top of this input window and permissions were granted.

A quick test on my Script and I was rockin’ and rollin’!

 

I know this is just a quicky blog post but please let me know if this helps you out!