2

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.

Improve this question
2
  • 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? Commented Feb 13, 2018 at 2:23
  • Aaron - what is the other way then? What if you want to run a script for all DBs in Azure? Commented Mar 31, 2020 at 14:21

2 Answers 2

Reset to default
2

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

Improve this answer
1
  • 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. Commented Nov 19, 2020 at 7:49
1

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];
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.