How to check whether database user already exists in Azure SQL Database

Question

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?

jrotello jrotello · Accepted Answer · 2016-11-21T15:54:38

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

How to check whether database user already exists in Azure SQL Database

2 Answers