2

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?
2
+50

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.

  • Can you use this to maintain logins and users, without the broken dependencies that Zach Smith observed? – Jon of All Trades Nov 30 '17 at 0:01
  • Yah, that's why I did the dacpacs. It was way to annoying to have all those login and user broken dependencies when I tried to bring our stuff under source control for the first time. – Anthony Genovese Nov 30 '17 at 0:16
  • @JonofAllTrades what didn't you like about this solution? I actually just had a new login reference issue pop up, and solved it with the method I posted. – Anthony Genovese Dec 1 '17 at 0:36
  • Frankly? I honestly don't see that it addresses the issue (that I'm trying to solve) at all. There's nothing here about logins or users. It seems to be a method of adding a database to a project, which is nice and all but I'm interested in having users which share a login across multiple databases. – Jon of All Trades Dec 1 '17 at 14:09
  • 1
    @JonofAllTrades let me know if that is good enough. I haven't had my coffee yet. – Anthony Genovese Dec 1 '17 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.