Problem
I'm trying to automate SQL deployment including User and Roles. It works when I create the user in Sql Management Studio, but when I deploy dacpack I get SqlException: Login Failed for user 'MyUser'
Description
I have SQL Server 2016 installed on localhost with both Sql Server and Windows authentication enabled.
I have Sql Database project in Visual Studio with these files:
MyUser.sql [build action=build]
CREATE USER [MyUser] WITH Password='$(MyUserPassword)';
RolesMembership.sql [build action=build]
ALTER ROLE [db_datareader] ADD MEMBER [MyUser] GO ALTER ROLE [db_datawriter] ADD MEMBER [MyUser]
I build it and deploy the created dacpac using command line:
SqlPackage.exe /Action:Publish /SourceFile:"MyDatabase.dacpac" /Profile:"MyDatabase.publish.xml" /v:MyUserPassword=c2Aaaaaaaaaaaa`
it works when when I execute this script in sql management studio. But when I deploy it using SqlPackage it leads to the
SqlException: Login failed for user 'MyUser'
EDIT:
I've investigated the generated sql script and in includes REVOKE CONNECT:
CREATE USER [MyUser]
WITH PASSWORD = N'c2Aaaaaaaaaaaa';
GO
REVOKE CONNECT TO [MyUser];
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'MyUser;
EXECUTE sp_addrolemember @rolename = N'db_datawriter', @membername = N'MyUser';
How to prevent the REVOKE CONNECT line being generated