Accessing non-Mobile Services tables in Server Scripts

This is a useful how-to if you are using Mobile Services as an addition to an already existing system. When setting up Mobile Services you need to specify a database. If you specify an existing database you can access the data from the tables within it but you must allow access to the Mobile Service user.

The problem is that you cannot use SQL Server Management Studio's nice graphical tools to do so since this is Sql Azure. It's time to write some queries!

Prereq - you are familiar with Mobile Services Server Scripts. If not, check out this great video from Nick Harris.

http://channel9.msdn.com/Series/Windows-Azure-Mobile-Services/Windows-Store-app-Validate-and-Modify-Data-with-Server-Scripts-in-Windows-Azure-Mobile-Services

The Scenario

Let's say you have a table in Mobile Services called Notification. (The tables are named directly after the POCOs, hence no pluralization at the moment). The main function of this table is to allow us to intercept insertions to it and send out notifications.

What we also can do is to query other tables within this script.

    var sql = 'select Name from Users where Id = ?';
   
    mssql.query(sql, [item.UserId],
     {
         success: function(results) {
              console.log("The name is " + results[0].Name);
         },
         error: function(error) {
             console.log('Error querying sql: ' + sql, error);
         }
     });

If you run this directly you will get an access denied message looking like this:

Error querying sql: select Name from Users where Id = ? { [Error: [Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT permission was denied on the object 'Users', database 'YourDatabase', schema 'dbo'.] sqlstate: '42000', code: 229 }

The fix is to grant rights to this user.

Step 1 - Identify the Mobile Services user

Open Sql Management Studio and select the database (in this case 'YourDatabase' in lack of better example naming.

Run

select * from sysusers where isSqlUser = 1 and hasDbAccess = 1

This will return a number of users. Look for the one having a name simular to AMJCKxGdWNLoginUser. (a bunch of random letters and the LoginUser).

Step 2 - Grant rights to this user

GRANT SELECT ON Users TO AMJCKxGdWNLoginUser

THAT'S IT, you are good to go!

In addition, if you need to insert or delete data then grant the appropriate permissions.