We help IT Professionals succeed at work.
Get Started

SQL SMO User.Create90 exception

1,267 Views
Last Modified: 2012-08-13
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."
    }

Open in new window

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE