CODEWITHSUNDEEP
sqlsql-serverpowershell
The_crouton
The_crouton

Reputation: 15

Powershell SQL create db, user, and add user to owner

I am fairly new to powershell and I am trying to create a script that creates a sql database, creates a user with password, and then adds that user to be the owner of the newly created database. The script will create the database but when I go into the users I do not see 'TestUser' there. 

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, 'TestDB2')
$db.Create()

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login($server, 'TestUser')
$login.LoginType = 'SqlLogin'
$login.PasswordExpirationEnabled = $false
$login.Create('PasswordGoesHere')



$server = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $server.Databases.Item('TestDB')
$db.SetOwner('TestUser', $TRUE)
$db.Alter()

Upvotes: 1

Views: 5586

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

It's almost always simpler to use TSQL and String Interpolation to do SQL Server administration than SMO. EG:

$userName = "SomeUser"
$dbName = "newdb"
$password = "*******"

$sql = @"
create login [$userName] with password = '$password';
create database [$dbName];
alter authorization on database::[$dbName] to [$userName];
"@

$sql
invoke-sqlcmd $sql

Upvotes: 2

GreyOrGray
GreyOrGray

Reputation: 1729

You won't see the user in the database because the owner of the database cannot be a user in the database. You can see this in SSMS if you try to make an existing user in the database the owner - you will receive an error that the 'proposed database owner is mapped as a user on this database'.

enter image description here

With powershell, this message isn't thrown, it just unmaps the user behind the scenes - so if you had previously seen the user in the database, you no longer would once you made it the owner. This isn't what you're experiencing, though because you never create the user in the database. You're just seeing the expected behavior.

Upvotes: 2

Related Questions