Error: SQL Azure “Grantor does not have GRANT permission.”

SQL Azure has few limitations when it comes to running T-SQL statements against the Azure User and Master databases. At times when you try to give minimal rights to the users in Azure or even try creating them, you may run into errors, sometimes they are easy to understand and at times absolutely unorthodox.

By now, I am sure you all know that you can’t always use the Azure Portal account to backup, drop, create databases and also it’s not appropriate to use the portal account and password in the connection string as that increases the risk of getting hacked and the whole Azure portal might have to be compromised.

Anyways, here is what I was trying to do, I wanted  to grant the user (Normal user with minimal rights) VIEW DATABASE STATE permission and also add that user to some of the SERVER ROLES and map it to a login. Sounds easy right? Indeed it is;-

So I executed the following statements one after the other (see the context of the query, that’s master). Remember you can only assign LoginManager and DbManager roles to a Non-admin account in SQL Azure, which is enough to carry out quite a few operations.

Query:

CREATE LOGIN NONDef WITH PASSWORD = 'Abc$5671983'

CREATE USER NONDef FROM LOGIN NONDef;

EXEC sp_addrolemember 'loginmanager', 'NONDef';

EXEC sp_addrolemember 'dbmanager', 'NONDef'

Result:

Command(s) completed successfully.

All the queries executed fine and worked great, then as I wanted to grant the user  SELECT, DELETE, UPDATE, VIEW DATABASE DEFINITION etc. for automating backups exports in SQL Azure and also for doing basic checks on the database, I landed up running the following query:

GRANT ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION TO NONDef;

and then the error, alright I know I was silly to do this, but the error was more sillier than I was, probably we need to handle this better.

 

 

So what did I do wrong here? guesses? Yeah I didn’t change the context of the query to the User Database against which I should have executed this query. Now as you cannot use USE DATABASE in SQL Azure, you can highlight the database (UserDB) and click new query in SSMS

After that you can execute the same query and it should work JUST FINE.

 

This is an informational Blog on the error which can be misleading to make sure you do not waste a lot of time troubleshooting this for no odd reason.

SQL Azure is superb! I am loving each and every feature of it and extremely excited about the amount of development going on in this space, exciting times for Microsoft and being an employee of MS, I feel extremely valuable as I get to work on the product directly and see the product mature with time, and Azure’s growth is exponential, tremendous functionalities and great updates every month.

Happy times in Cloud!!!