Create an new administrator level login in Azure S

Question

I need to replace my old admin login/user with a new one. I tried the following:

CREATE LOGIN newDbAdmin WITH password='123isTheBestPasswordEver'
CREATE USER newDbAdmin

With this I'm then also able to log into Azure SQL via Microsoft SQL Server Management Studio. However it doesn't seem to be an admin level login+user. I'm unable to create tables and a few other things that admins can do. I suspect I need to GRANT permissions to certain schemas (dbo?) or something along those lines ...

So, what's the right way to create user+login on Azure SQL with the same level of privileges as the original admin (created when I create the DB via the Azure portal site).

On a related note, I assume the proper way to dispose off the old login is:

DROP USER oldAdmin
DROP LOGIN oldAdmin

?

Answer 1

you just created a login and a corresponding user, you have to add the appropriate role memberships...

e.g.,

EXEC sp_addrolemember 'dbmanager', 'login1User';

EXEC sp_addrolemember 'loginmanager', 'login1User';

see: https://azure.microsoft.com/documentation/articles/sql-database-manage-logins/

Answer 2

You can use Azure AD to manage the server. Create a DBA group - assign all DBAs to that group. Then assign the group as the AD Manager for the server - that allows you to have multiple DBAs

Anyone in that group will have full DBA permission over the server.

https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-aad-authentication

Ideally, I think they expect you to use DB level contained users granted by a DBA. For us, we needed multiple admins as we have a lot of databases.

Answer 3

You can only have one server-level admin. You can create as many other logins and users as you like but they need to be granted access to the individual databases separately. You can reset the password for the server-level admin via the azure portal.

There's no server role in Azure SQL Database that grants access to all databases. dbmanager lets you create databases, loginmanager lets you create logins, but the server-level principal login must be used to grant access to individual databases.

To create a new user and give dbo rights to one or more databases:

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

And yes, you drop users in the same way you would in on-premises sql.

If you wanted to make a new user as dbo on all databases on a server you can use a little powershell to make your life easier:

$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"; `
}