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?
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.
Look into the Contained Database User Model (https://msdn.microsoft.com/en-us/library/ff929188.aspx).
sys.server_principals doesn't exist in SQLAzure,SYS.SQL_Logins inherits from sys.server_principals all the columns, plus few more additional columns