Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE LOGIN newDbAdmin WITH password='123isTheBestPasswordEver'
- CREATE USER newDbAdmin
- DROP USER oldAdmin
- DROP LOGIN oldAdmin
- -- in master
- create login [XXXX] with password = 'YYYYY'
- create user [XXXX] from login [XXXX];
- -- if you want the user to be able to create databases and logins
- exec sp_addRoleMember 'dbmanager', 'XXXX';
- exec sp_addRoleMember 'loginmanager', 'XXXX'
- -- in each individual database, to grant dbo
- create user [XXXX] from login [XXXX];
- exec sp_addRoleMember 'db_owner', 'XXXX';
- $newSqlUser = 'YOUR_NEW_LOGIN_HERE';
- $serverName = 'YOUR-SERVER-NAME.database.windows.net'
- $sqlAdminLogin = 'YOUR-ADMIN-SQL-LOGIN'
- $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.
- # Don't need the username param here but can be nice to avoid retyping
- $sqlCreds = get-Credential -Username $sqlAdminLogin -Message 'Enter admin sql credentials'
- # Create login and user in master db
- $sql = "create login [$newSqlUser] with password = '$newSqlPassword'; create user [$newSqlUser] from login [$newSqlUser];"
- invoke-sqlcmd -Query $sql -ServerInstance $serverName -Database 'master' -Username $sqlCreds.UserName -Password ( $sqlCreds.GetNetworkCredential().Password )
- "new login: $newSqlUser"
- "password: $newSqlPassword"
- # 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' -Username $sqlCreds.UserName -Password ( $sqlCreds.GetNetworkCredential().Password ) | `
- 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 -Username ($sqlCreds.UserName) -Password $( $sqlCreds.GetNetworkCredential().Password ); `
- "created user in $db database"; `
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement