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

Question

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()

Have you considered using the SQL Server DSC resource for this task? It would be simpler than trying to script it out. — Maximilian Burszley
– Maximilian Burszley, Commented Sep 18, 2018 at 17:55

GreyOrGray · Accepted Answer · 2018-09-18 18:01:19Z

2

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'.

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.

answered Sep 18, 2018 at 18:01

GreyOrGray

1,73811 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Browne - Microsoft · Accepted Answer · 2018-09-18 18:33:14Z

2

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

answered Sep 18, 2018 at 18:33

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

2 Comments

gvee Over a year ago

Whilst I advocate the use of T-SQL statements for this job; string interpolation is not the way to do it. xkcd.com/327

David Browne - Microsoft Over a year ago

This is a powershell script, not a end-user program, so presumably the string inputs are coming from a trusted person. And SQL Server does not support parameterization of DDL. So one way or another, you're building a dynamic TSQL batch.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related