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

My new client is planning to use Azure managed SQL Database services. I am using dacpac in order to deploy the database. In the dacpac, I have a post-deployment script in order to create a sql user as follows

我的新客户端计划使用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

However, it is throwing the following error when I try to apply dacpac in Azure (which is compiled with target platform - Microsoft Azure SQL Database V12).

但是，当我尝试在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:

From the message it seems that I can not depend the sys.server_principals object.

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

How can I overcome this? Is there any equivalent for this?

我怎么能克服这个？这有什么相同的吗？

3 个解决方案

#1

You need to drop checking of SQL login name in your SQL script for dacpac deployment to Azure SQL database.

您需要在SQL脚本中删除对SQL登录名的检查，以便将dacpac部署到Azure SQL数据库。

This is because in Azure SQL Server, you can only check against the SQL login name in the sys.sql_logins table in the master database.

这是因为在Azure SQL Server中，您只能检查master数据库中sys.sql_logins表中的SQL登录名。

In addition, Azure SQL Database also does not support reference to another database even though they are in the same Azure SQL Server.

此外，Azure SQL数据库也不支持对另一个数据库的引用，即使它们位于同一个Azure SQL Server中。

Hope this helps.

希望这可以帮助。

#2

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

sys.server_principals doesn't exist in SQLAzure,SYS.SQL_Logins inherits from sys.server_principals all the columns, plus few more additional columns

SQLAzure中不存在sys.server_principals，SYS.SQL_Logins从sys.server_principals继承所有列，再加上几个额外的列

#3

Look into the Contained Database User Model (https://msdn.microsoft.com/en-us/library/ff929188.aspx).

查看包含数据库用户模型（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.

在包含的数据库用户模型中，master数据库中的登录名不存在。相反，身份验证过程发生在用户数据库中，而用户数据库中的数据库用户在主数据库中没有关联的登录名。

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

#1