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.
No comments:
Post a Comment