sql-server - 如何检查Azure SQL数据库中是否已存在数据库用户

Question

我的新客户计划使用 Azure 托管 SQL 数据库服务。我正在使用 dacpac 来部署数据库。在 dacpac 中，我有一个部署后脚本，用于创建 sql 用户，如下所示

IF NOT EXISTS (SELECT name
               FROM   sys.server_principals
               WHERE  name = 'myusername')
    BEGIN
        CREATE LOGIN myusername
            WITH PASSWORD = '******';
    END
GO

但是，当我尝试在 Azure 中应用 dacpac(使用目标平台 - Microsoft Azure SQL 数据库 V12 编译)时，它会引发以下错误。

An error occurred while the batch was being executed.
Updating database (Failed)
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 4 Invalid object name 'sys.server_principals'.
Error SQL72045: Script execution error.  The executed script:

从消息来看，我似乎不能依赖 sys.server_principals 对象。

我该如何克服这个问题？有没有类似的东西？

Answer 1

查看包含的数据库用户模型 ( https://msdn.microsoft.com/en-us/library/ff929188.aspx )。

In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database.

IF NOT EXISTS (
    SELECT  [name]
    FROM    sys.database_principals
    WHERE   [name] = 'myusername'
)
BEGIN
    CREATE USER [myusername] WITH PASSWORD = '********';
END