Delete multiple users in Azure SQL Database

Question

I'm trying to write a script auto import database to azure but after finishing the import, I want it will delete old users in the database because that user backup from the different environments so I need to clear them to meet security regularly. but I was stuck in the end line of the script because USE is not supported in Azure SQL Database. Does anyone have a script or method for solving this case? Thank you for sharing!

ERROR:
"USE statement is not supported to switch between databases. Use a new connection to connect to a different database. "

Script:

$DBServer = 'dev01.database.windows.net'
$DbUser = 'admin'
$DBPassword = '12131231'
$Backupfilename = 'Catalog-2022-08-23.bacpac'
$DBName = 'Catalog'

$query = "
USE $DBName
GO
DECLARE @commandcommand nvarchar(max) = ''

SELECT @commandcommand += 'DROP USER ['+name+'];'
FROM sys.sysusers
WHERE name like 'au%'

EXEC (@commandcommand );
"

Restore file to SQL Server

Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Query "Drop database [$DBNameimport]"
Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Query "Create database [$DBNameimport]"
sqlpackage.exe /Action:Import /tsn:tcp:$DBServer,1433 /tdn:$DBNameimport /tu:$DbUser /tp:"$DBPassword" /sf:$PSScriptRoot\$Backupfilename /p:Storage=File
Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Query $query

Answer 1

I have finished the script auto download file backup from storage to import for a new environment and delete older users from file backup to meet security:

$DBServer = 'dev01.database.windows.net'
$DbUser = 'admin'
$DBPassword = '12131231'
$StorageAccountName = 'adminstorage'
$StorageAccountKey = 'sdfsdfsdf'
$ContainerName = 'Catalog-backup'
$Backupfilename = 'Catalog-2022-08-23.bacpac'
$DBName = 'Catalog'

Copy file from blob storage

$ctx = New-AzStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

write-output "backup file name: $Backupfilename"
Get-AzStorageBlobContent -Blob $Backupfilename -Container $ContainerName -Destination $PSScriptRoot -Context $ctx

Check name database

Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Query "Drop database [$DBNameimport]"
Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Query "Create database [$DBNameimport]"

Restore file to SQL Server

sqlpackage.exe /Action:Import /tsn:tcp:$DBServer,1433 /tdn:$DBNameimport /tu:$DbUser /tp:"$DBPassword" /sf:$PSScriptRoot\$Backupfilename /p:Storage=File

Delete older user when finish import

$query = "

DECLARE @commandcommand nvarchar(max) = ''

SELECT @commandcommand += 'DROP USER ['+name+'];'
FROM sys.sysusers
WHERE name like 'au%'

EXEC (@commandcommand );
"
Invoke-Sqlcmd -ServerInstance tcp:$DBServer -Username $DbUser -Password "$DBPassword" -Database $DBNameimport -Query $query

Answer 2

I see you have self-answered your question. In summary, avoid the USE statement in the T-SQL script and instead specify the -Database parameter with Invoke=Sqlcmd.

Adding that the behavior of aggregate string concatenation (i.e. @Command += 'DROP USER ['+name+'];') is undefined and may return unexpected results in some cases. It would be better to use STRING_AGG for this task like the below example.

$query = "  
  
DECLARE @Command nvarchar(max) = '';  
  
SELECT @Command = STRING_AGG(N'DROP USER ' + QUOTENAME(name),';')  
FROM sys.sysusers  
WHERE name like N'au%';  
  
EXEC (@Command);  
"