在Azure SQL中创建新的管理员级别登录名？ [英] Create an new administrator level login in Azure SQL?

问题描述

我需要将我的旧管理员登录名/用户替换为新的。我尝试了以下操作：

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

然后，我还可以通过Microsoft SQL Server Management Studio登录到Azure SQL。但是，它似乎不是管理员级别的登录名+用户。我无法创建表格以及管理员可以执行的其他操作。我怀疑我需要授予对某些架构（dbo？）或类似内容的权限...

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

因此，在Azure上创建用户+登录的正确方法是什么具有与原始管理员相同级别的特权的SQL（当我通过Azure门户网站创建数据库时创建）。

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

？

推荐答案

您只能拥有一个服务器级别的管理员。您可以根据需要创建任意数量的其他登录名和用户，但是需要分别授予它们访问各个数据库的权限。您可以通过azure门户重置服务器级管理员的密码。

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.

Azure SQL数据库中没有服务器角色可授予对所有数据库的访问权限。 dbmanager 允许您创建数据库， loginmanager 允许您创建登录名，但是必须使用服务器级主体登录名来授予权限

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.

要创建新用户并为一个或多个数据库赋予dbo权限，请执行以下操作：

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

是的，删除用户的方式与在本地sql中使用的方式相同。

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

如果您想在服务器上的所有数据库上以dbo身份创建新用户，则可以使用一些powershell来简化生活：

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

这篇关于在Azure SQL中创建新的管理员级别登录名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！