=> Use sqlps
=> provide correct parameters for $newSqlUser, $serverName, $sqlAdminLogin, $sqlAdminPassword
PS code:
=======
Import-Module SQLPS -DisableNameChecking
$newSqlUser = ‘test3’;
$serverName = ‘sqlazure.database.windows.net’
$createAdminUser = $TRUE;
# generate a nice long random password
Add-Type -Assembly System.Web
$newSqlPassword = [Web.Security.Membership]::GeneratePassword(25,3) -Replace ‘[%&+=;:/]’, “!”;
# prompt for your server admin password.
#resets the variable
$results = @()
# login using SQL authentication, which means we supply the username
# and password
$credential = Get-Credential
#the backslash is regular expression to remove \ (prompt will give it as default for domain)
$userName = $credential.UserName -replace(“\\”,””)
#getnetworkcredential gives the passport unencrypted
$pass = $credential.GetNetworkCredential().password
# Create login and user in master db
$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’;”
invoke-sqlcmd -Query $sql -ServerInstance $serverName -Database ‘master’ -U $userName -P $pass
“new login: $newSqlUser”
“password: $newSqlPassword”
# Create login and user in master db
if ( $createAdminUser ) { `
$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’; create user [$newSqlUser] from login [$newSqlUser];exec sp_addRoleMember ‘dbmanager’, ‘$newSqlUser’; exec sp_addRoleMember ‘loginmanager’, ‘$newSqlUser’;”;`
} else { `
$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’; create user [$newSqlUser] from login [$newSqlUser];”;`
}
# sql to create user in each db
if ( $createAdminUser ) { `
$createUserSql = “create user [$newSqlUser] from login [$newSqlUser]; exec sp_addRoleMember ‘db_owner’, ‘$newSqlUser’; “; `
} else { `
$createUserSql = “create user [$newSqlUser] from login [$newSqlUser]; exec sp_addRoleMember ‘db_datareader’, ‘$newSqlUser’; exec sp_addRoleMember ‘db_denydatawriter’, ‘$newSqlUser’;”; `
}
# can’t have multiple Invoke-SQLCmd in a pipeline so get the dbnames first, then iterate
$sql = “select name from sys.databases where name <> ‘master’;”
$dbNames = @()
invoke-sqlcmd -Query $sql -ServerInstance $serverName -Database ‘master’ -U $userName -P $pass | `
foreach { $dbNames += $_.name }
# iterate over the dbs and add user to each one
foreach ($db in $dbNames ) { `
invoke-sqlcmd -Query $createUserSql -ServerInstance $serverName -Database $db -U $userName -P $pass; `
“created user in $db database”; `
}
SQLServerWiki 