One thing that threw me the first time I connected to an Azure SQL Database in SSMS using a non-admin account was the requirement to specify the database name on connection.
When connecting for the first time to either an installed SQL Server instance (e.g. on-premises or IaaS) or an Azure SQL Server (the logical container that holds the Azure SQL Databases), SSMS will default to connecting to the master database. This isn’t a problem for admin logins because they have access to the master database in both cases. However non-admins do not have access to the master database on an Azure SQL Server & the connection attempt will fail with the following error.
To resolve this, click on the Options button in the Connection pop-up.
In the Connections Properties, type the name of the database you want to connect to in the “Connect to database” field. Initially this will be set to default (i.e. master) &, unlike on an installed SQL Server instance, you will not be able to expand the list to see all the databases on the Azure SQL Server. So you must type in the field.
Click Connect & the connection should succeed, assuming your non-admin user has access to the database you specified.
Another thing to be aware of is that the database specification persists for future connections to the Azure SQL Server. So if you attempt to connect using your admin login at a later time, you will automatically be connected to the specified database rather than master, unless you change the value in the “Connect to database” field. When this happens, you won’t see other databases on the Azure SQL Server or options like the Security node.
This can be resolved by changing your connection back to master, as below.
Be First to Comment