Delete multiple users in Azure SQL Database

Hoàng Lê Hiệp Minh 51 Reputation points
2022-08-31T07:39:33.713+00:00

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

Azure SQL Database
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Hoàng Lê Hiệp Minh 51 Reputation points
    2022-08-31T08:42:07.91+00:00

    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

    0 comments No comments

  2. Dan Guzman 9,206 Reputation points
    2022-08-31T09:28:47.663+00:00

    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);  
    "