How to check whether database user already exists

问题:

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

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

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.

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

回答1:

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

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.

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

Hope this helps.

回答2:

Look into the Contained Database User Model (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

回答3:

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