Tuesday, March 10, 2015

Microsoft Azure error - "Cannot open database "master" requested by the login"

I've been busy setting up a database for a TSQL programming class that I'll be teaching in a few weeks. I've run into a few things that made me scratch my head. Here's one of them.

I created a sample database, a new login, and a user to go with the login. Then I gave the user read only access to the database, via sp_addrolemember.

When I used that login to connect via SSMS (File, Connect Object Explorer, enter the new login, also click on the Options button to specify the correct database), it appears to connect fine, and I see the connection.

However, when I expand the databases tab, and try to right-click the sample database to get the New Query option, I get the message "Cannot open database "master" requested by the login. The login failed.". So even though I was trying to open a query in the sample database, it tried accessing the master database.


Workaround:
This is a strange one, but the workaround is to right click on the connection object, and not the database. This will allow you to open up a new query, and will automatically put you in the database you specified in the connection options.

Doing this involves a bit of rethinking, because most developers who have worked extensively with SSMS are accustomed to right clicking on a database and selecting New Query to open a connection. But this won't work with Azure, unless you're also a user in the Master database.

Hopefully this write-up will save a few people some time when they go searching for details on this issue. Feel free to comment with more information.




No comments:

Post a Comment