How to change the current database in an SQL Query window in SSMS?

Question

I'm trying to create a SQL script that creates a new server login in the master database, and then creates a corresponding user in an existing database.

If the 'USE [database_name]' appears more than once in the query editor, it results in two errors:

Msg 40508, Level 16, State 1, Line 4 USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

Msg 40508, Level 16, State 1, Line 11 USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

I've considered turning on SQL CMD mode and using the :CONNECT command, but I don't want to connect to a different database server, just change the database. Connecting to a different database (or even the same one), would require me to embed a username and password in the script as well, which is not desirable. I just want to open the SQL script connected to the right server, and be able to switch between databases.

• Aaron Bertrand: If you’re using Azure SQL Database, you can’t switch to master to create a login. What if you want to create a login named Frank, and so does someone else on the same server? What if they see the login Frank and think it’s theirs that they already created, and change the password on you?
• Naomi: Aaron - what is the other way then? What if you want to run a script for all DBs in Azure?

Answer 1

Read this link

This issue occurs because Azure SQL Database does not support the "use" command.

You have to install the latest cumulative update for SQL Server

• Tahir Alvi: So, when the database is hosted on Azure and someone tries to use it via SSMS what update needs to be installed? I am using SSMS v 17.9.5.

Answer 2

If Azure allows you to reference Stored Procedures in other Databases using 3-part names, then you might could try executing the command using Dynamic SQL via sp_executesql:

EXEC [master].[dbo].[sp_executesql]
 N'CREATE LOGIN [Bob] WITH PASSWORD = ''fgdfgdfgdfgfd'';';
CREATE USER [Bob] FOR LOGIN [Bob];