应如何在Azure中的地理冗余数据库上创建登录名和用户

Question

我需要在地理复制数据库上创建一个登录名和用户。用户将被授予此数据库中"db_datarader"的角色。

尝试的方法是使用SID（相同身份）参数，如下所示：

-- 1. PRIMARY_DATABASE: Run on master database of Primary Database to create login
CREATE LOGIN [LOGINUSER] WITH password='XYZ'
GO
-- 2. Select details for created SQL login
SELECT [name], [sid] 
FROM [sys].[sql_logins] 
WHERE [type_desc] = 'SQL_Login'
AND [name] = 'LOGINUSER'
GO
-- 3. SECONDARY_DATABASE: Run on master database of Secondary Database to create login with associated SID
CREATE LOGIN [LOGINUSER]
WITH PASSWORD = 'XYZ',
SID = <SID from Step 2>
GO

虽然登录创建在两个数据库上都可以正常工作，但我被允许访问PRIMARY_DATABASE，并被拒绝访问SECONDARY_DATABASE（出现以下错误）：

The server principal "LOGINUSER" is not able to access the database "master" under the current security context.
Cannot open user default database. Login failed.
Login failed for user 'LOGINUSER'. (Microsoft SQL Server, Error: 916)

1. 这是在地理复制数据库中设置登录的正确方法吗
2. 如何在这些数据库上设置用户

Answer 1

您也应该使用从主服务器获取的SID在其他服务器上创建登录。否则，您可以使用SQLServer/db中的包含用户功能，以便将用户自动复制到只读副本。

Answer 2

我遇到了同样的问题，下面的问题对我有效。我有Azure SQL Server，它启用了活动地理复制。

1. 请确保将您的IP地址添加到现有服务器和新服务器的防火墙中

在主服务器的master数据库上：

2. 创建登录

CREATE LOGIN geo_user WITH PASSWORD = 'secure-password'

3. 创建一个相应的用户并将其分配给数据库管理员角色（您可以根据自己的要求分配适当的角色）

CREATE USER geo_user FOR LOGIN geo_user
ALTER ROLE dbmanager ADD MEMBER geo_user 

4. 注意新登录的SID：

SELECT sid FROM sys.sql_logins WHERE name = 'geo_user'

在主服务器的source数据库上：

5. 为相同登录创建用户：

CREATE USER geo_user FOR LOGIN geo_user

6. 将用户添加到数据库所有者角色（_O）：

ALTER ROLE db_owner add MEMBER geo_user

在辅助服务器的master上：

7. 创建与主服务器上相同的登录名（使用步骤4中相同的用户名、密码和SID）：

CREATE LOGIN geo_user with password = 'secure-password', sid=0x01060000000000640000000000000000A0D03563024DA846ADBCF33D31B6C026

8. 创建相应的用户并将其分配给数据库管理员角色：

CREATE USER geo_user FOR LOGIN geo_user
ALTER ROLE dbmanager ADD MEMBER geo_user 

现在，您应该能够使用该用户登录到新的服务器并查询数据库。

Answer 3

您似乎没有对master数据库的本地访问权限。您是否尝试过通过其他登录访问master？如果没有，你可能想在master中创建一个本地用户并使用它进行连接。

Answer 4

您应该使用contained users。这些用户包含在数据库中，与地理复制同步，不需要从master表中进行额外登录。

创建一个包含的用户，密码为db_owner:

USE mydatabase;
CREATE USER myuser WITH PASSWORD = 'secret';
EXEC sp_addrolemember 'db_owner', 'myuser'

更多信息：https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15