SQL SMO User.Create90 exception

I am sure I am doing something simple wrong, but I have a script that I am trying to put together to create a bunch of databases and create logins and users for them.

Script still has a long way to go before I get it into prod, however I am tripping up when trying to create the user.

When it tries to run the line:

$dbuser.create()

Following error is show:

Exception calling "Create" with "0" argument(s): "Create failed for User ''. "
At C:\workbook\DatabaseCreation Scripts\CreateDatabase.ps1:72 char:23
+         $dbuser.create <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

The code below runs in a function but I have stripped out the stuff that isn't relevant.

$username = "test"

    #Setup SMO Server object with conenction to self
    $SQLinstance = "IT-SQL"
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLinstance 
    
    #setup variables for checking if DB exists (also used to create DB if it doesn't exist)
    $dbname = $username
    $datafilename = $dbname + '_Data'
    $datafilepath = $s.Information.MasterDBPath + '\' +  $datafilename + '.mdf'
    
    #Check if database already exists by checking datafile path. This is the quickest way to check as it doesn't require us to enumerate through all the databases.
    if (test-path $datafilepath){
        Write-Warning "Database already exist for user $username. Skipping create!"
        return
    }
    
    # Instantiate the database object and add the filegroups
    $db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
        
    $dbfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
    $db.FileGroups.Add($dbfg)

    # Create the file for the system tables
    $dbdatafile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($dbfg, $datafilename)
    $dbfg.Files.Add($dbdatafile)
    $dbdatafile.FileName = $datafilepath
    $dbdatafile.Size = [double](5.0 * 1024.0)
    $dbdatafile.GrowthType = 'Percent'
    $dbdatafile.Growth = 10.0
    $dbdatafile.IsPrimaryFile = 'True'
    $dbdatafile.MaxSize = [double](100.0 * 1024.0)

    # Create the file for the log
    $loglogname = $dbname + '_Log'
    $dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
    $db.LogFiles.Add($dblfile)
    $dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
    $dblfile.Size = [double](10.0 * 1024.0)
    $dblfile.GrowthType = 'Percent'
    $dblfile.Growth = 10.0
    
    # Create the database
    $db.Create()
    if ($db.state -eq "Existing"){
        Write-host "Database for user $username successfully created"
    }
    else{
        Write-Warning "Database for user $username did not create succesffully!!! Skipping setting permissions"
        return
    }
    
    #Check if student already has a login to SQL
    $logins = $s.Logins
    
    $studlogin = $logins | where{$_.Name -eq “insearch1\$username”}
    if ($studlogin -eq $null){
        Write-Host "SQL Login for $username doesn't exist, creating"
        $sqllogin = new-object ('Microsoft.SqlServer.Management.Smo.Login') ($s, "insearch1\$username")
        $sqllogin.LoginType = "WindowsUser"
        $sqllogin.Create()
        $sqllogin.DefaultDatabase = $dbname
        $sqllogin.Alter()
        
        $dbuser = new-object ('Microsoft.SqlServer.Management.Smo.User') ($db, $Login.Name)
        $dbuser.UserType = "SqlLogin"
        $dbuser.Login = $sqllogin
        $dbuser.create()
        
    }
    else{
        Write-Host "SQL Login for $username already exists."
    }

Select all Open in new window