User Created Using DACPAC Cannot Log On

Question

I have a Visual Studio 2013 SQL Database Project which creates a DACPAC file for deployment of the SQL database to SQL Server using the "Deploy Data-tier Application..." option is SQL Server Management Studio (SSMS).

As part of the project, there is a Security folder with an SQL script in it that creates the required user. The script is:

CREATE LOGIN [MyLogin]
    WITH PASSWORD = N'MyPassword',
    DEFAULT_DATABASE = [MyDatabase],
    DEFAULT_LANGUAGE = [us_english],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF
GO

CREATE USER [MyLogin] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[dbo];
GO

exec sp_addrolemember 'db_datareader', N'MyLogin'
GO

GRANT EXECUTE ON OBJECT::[dbo].[MyObject1] TO [MyLogin];
GO

GRANT EXECUTE ON OBJECT::[dbo].[MyObject2] TO [MyLogin];
GO

GRANT EXECUTE ON OBJECT::[dbo].[MyObject3] TO [MyLogin];
GO

When publishing the DACPAC to SQL Server, the application that consumes the database is unable to log on. Viewing the user properties in SSMS show that there is no association between the database user and the SQL Server login principal, which presumably prevents the application from logging in.

However, manually running the above script verbatim in SSMS creates the user correctly and the application can log in successfully.

Why does the DACPAC-created user not have the database mapping setup correctly, and what needs to be done in the script and/or DACPAC properties to get everything working as intended.

Thanks, Martin

Answer 1

So eventually I got to the bottom of this with a colleague's help, and the missing part was the following two lines after the CREATE USER ... statement:

GRANT CONNECT TO [MyLogin];
GO

Now the DACPAC correctly deploys the SQL database and applications can connect to the database using the created MyLogin user.