Create User in dacpac deployed by SqlPackage.exe l

2019-07-09 09:03发布

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

  1. I have SQL Server 2016 installed on localhost with both Sql Server and Windows authentication enabled.

  2. 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]
      
  3. 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

0条回答
登录 后发表回答