We help IT Professionals succeed at work.

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:


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!"
    # 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')

    # Create the file for the system tables
    $dbdatafile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($dbfg, $datafilename)
    $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)
    $dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
    $dblfile.Size = [double](10.0 * 1024.0)
    $dblfile.GrowthType = 'Percent'
    $dblfile.Growth = 10.0
    # Create the database
    if ($db.state -eq "Existing"){
        Write-host "Database for user $username successfully created"
        Write-Warning "Database for user $username did not create succesffully!!! Skipping setting permissions"
    #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.DefaultDatabase = $dbname
        $dbuser = new-object ('Microsoft.SqlServer.Management.Smo.User') ($db, $Login.Name)
        $dbuser.UserType = "SqlLogin"
        $dbuser.Login = $sqllogin
        Write-Host "SQL Login for $username already exists."

Open in new window

Watch Question


Just to be clear, the script creates the database and the Login fine, it just doesn't create the user.

Cheers for any help.
Solved it:

It didn't seem to like me trying to set the UserType.

Following is how I got it to work (not $db is the database object for the database I am trying to add as a user and $login is the login object that was created on the server).

$dbuser = new-object ('Microsoft.SqlServer.Management.Smo.User') ($db, $Login.Name)
$dbuser.DefaultSchema = "dbo"
$dbuser.Login = $login.Name

If somone could point me to a good resource that shows what properties I need to set for objects in powershell before you can create them that you can have the points (powershell errors don't tell you anything)!


Further testing also revealed I actually should have been passing $dbuser.Login  $login.Name and not the $login object itself. It looks like this was the actual issue.