0

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.

  • 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? – Aaron Bertrand Feb 13 '18 at 2:23
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

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];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.