How to create a user for a login in 2013 SQL Server Database Project

Question

I'm trying to create a user with a login for a SSDT database project. The login already exists on the target server. With the following SQL:

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

GO

I get the error:

Error 1 SQL71501: User: [MyLogin] has an unresolved reference to Login [MyLogin].

I've found two solutions, but neither work with 2013:

1) Create a server project to reference the login. This isn't an option in the current version of SSDT / VS2013

2) Disable schema checking. The option is missing in 2013 (I believe it was Options -> Database Tools -> Schema Compare)

Answer 1

You can actually create the Server Level Login within the SQL Server Database Project using the standard T-SQL Syntax:

CREATE LOGIN [Login_Name] WITH PASSWORD = '<Password>';

Note: You can also right-click the database project and choose 'Add New Item' and navigate to SQL Server > Security (within the templates dialog) and select 'Login (SQL Server)'.

This resolves the SQL71501 Error and (assuming you are using SQLPackage.exe for deployment) will allow SQLPackage.exe the ability to compare the security object with the target Database before deployment and publishing occurs.

Hope that helps :)