How do you reference server logins from a visual Studio database project

database-projectsvisual studio 2017

I'm looking for a way to move all our SQL Server databases (on one server) into source control.

I tried creating a solution, and then adding VS database projects to the solution that correspond to the SQL Server databases.

As I was creating the projects (via importing existing databases) I chose to import security objects.

These security objects are users that need to reference logins of some kind. i.e. in a database project I have a folder Security with the SQL script: CREATE USER xxx. The VS debugger underlines this in red and warns me:

SQL71501: User: xxx has an unresolved reference to Login xxx.

To fix this I tried creating a new project called SERVER, in which I would include server-related SQL files.

  • How do I reference this new project from the other database project?

Best Answer

Right click on references in your SSDT project. Click on Add Database reference.

enter image description here

Fill out the database reference information here. I bring in the server I am connecting to as a dacpac object, then reference it as a project in the current solution. Seems to work best for me.

EDIT: I just tested using a database project in the same solution as well and it resolved the references correctly.

enter image description here Edit 2: Why this works - Projects within a VS solution do not "talk" to each other, by default. In the real world, servers and databases do "reference" each other. What we are doing here is explicitly making that connection, because the scope of a SSDT project can only be one database. Once you add the SSDT project and sync all the objects, including the security, you need to add that project as a reference in order for it "see" the database objects at the server level.

Related Question