SQLServerWiki

“The Only Thing That Is Constant Is Change”

Powershell script to create admin/normal user for all the databases in SQL Azure instance.

Posted by database-wiki on March 20, 2017

=> 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”; `
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: