SSDT Unresolved References SQL71501

Question

I have an issue in SSDT.  I have tried various solutions to the problem but none seem to resolve.

We did a schema comparison.  Imported the changes into the project and now get unresolved references to Login's and Roles.

What is the right way to solve this problem. 

MSSQL 2008r2 VS2010 SSDT and Powertools installed.

Errors:

Error 1 SQL71501: Role Membership: <unnamed> has an unresolved reference to object [dbUser]

Error 7 SQL71501: User: [dbUser] has an unresolved reference to Login [dbUser]

I do not want to just supress the error or manage the permissions in SSMS.

I want to properly reference the Server objects.

Many of the solutions metion making a server project and referencing from the databse project. This method does not seem to work for SSDT.

Thanks in advance.

Answer 1

Hi Max,

You're correct that the notion of a server project is no longer needed with SSDT.  Instead, you can add the necessary server objects to your project directly by choosing new login, user, etc. from the Add New Item dialog.  If the same server objects are consumed by multiple database projects, it may make sense to create a separate SSDT project for your server objects and add a database reference to it from your projects.

Let me know if this doesn't resolve your issue.

Thanks,

Janet Yeilding

Answer 2

Thank you for that answer. That totaly resolves my issue. 

Why is the machine generated code not right?

I made some test users and roles the way you suggested.

I watched the errors disappear as I changed the objects to match mine.

But I was editing the machine generated files by the schema compare.  I will repeat the test in the near future, and see if it is repeatable or if it overwrites the code I modified.  I wonder if it is the way we have the database users or roles built that is causing the issue?

Thanks again, very fast response, accurate answer, great job.

Answer 3

The reason it didn't add the logins when you first tried it is that a schema compare does not include server-scoped objects by default.  If you try a schema compare without changing any settings, it will just ignore login objects.  If you click the 'Options' button in a Schema Compare window and select the "Object Types" tab, you can make your schema compare include logins, as well as other global settings like server roles.

I hope this is useful,
Tom

Answer 4

That makes perfect sense.  I was able to verfiy that, so thanks again.  Overall a great tool.  We are doing our first deployment via SSDT to our QA environment next week.  Thanks again.

Answer 5

How about the temporary tables showing up as unresolved references, there should be a straight way of resolving them instead of suppressing or writing dummy code of create #table.. 

what so great about this tool @DeveloperMax, you were superexcited about this :( , it has fundamental flaws... 

Bless you!

---

HydPhani

Answer 6

Hi Janet,

I am having a similar problem. I've done as you suggested and created a second db project to store logins and users. 

However when I do something like this in one of my .sql files:

GRANT UPDATE
    ON OBJECT::[dbo].[SomeTable] TO [IDS.UserName]
    AS [dbo];

I am getting the SQL71501 unresolved reference error.

Here IDS is the database name used when creating a database reference to the database project that stores the logins and users.

What am I doing wrong?

Thanks!

Kevin

Answer 7

Hi Kevin, the “Add Database Reference” help is useful in understanding this. You need to use the SQLCMD variable for the referenced database - usually if the project is called "IDS" the variable will be "$(IDS)". The other thing to watch our for is that you probably want to use "[$(IDS)].[UserName]" as [IDS.UserName] will be treated as a single name, not a multi-part name. Finally, the user may be part of a schema in which case "[$(IDS)].[dbo].[UserName]" would be the correct way to write this.

Thanks,

Kevin Cunnane

Answer 8

Hi Kevin,

Thanks for answering so quickly. Unfortunately I tried all of those combinations (and many others) before posting this question, and I am still getting the same error message. :-(

Answer 9

Just to be clear

GRANT UPDATE
    ON OBJECT::[dbo].[Informatics_SxDI] TO [$(IDS)].[UserName]
    AS [dbo];

actually produces a different error message:

SQL80001: Incorrect syntax near '.'.

In reference to the '.' between the [$(IDS)] and the [UserName]

Any other combination of using the SQLCMD variable or the Literal name that doesn't generate this parsing error gives the SQL71501 unresolved reference error. 

Thanks!

Answer 10

Thanks.....Issue resolved. :)

Answer 11

The reason it didn't add the logins when you first tried it is that a schema compare does not include server-scoped objects by default.  If you try a schema compare without changing any settings, it will just ignore login objects.  If you click the 'Options' button in a Schema Compare window and select the "Object Types" tab, you can make your schema compare include logins, as well as other global settings like server roles.

I hope this is useful,
Tom

I have done what you say above, but the SQL71501 errors still appear even if I include Logins under Non-application-scoped object types and re-compare.