SSDT & TFS: compare SQL Server database not working

Question

I have a database project with SSDT (SQL Server Data Tools) in Visual Studio 2015 and TFS source control.

This morning while I was trying to compare two databases (DB -> TFS), it didn't work and I got an error:

Comparison completed. Difference detected. Restricted comparison. See error list for detail

And in error list window warning was:

Severity Code Description Project File Line Suppression State Warning The login for the target does not have the VIEW ANY DEFINITION permission. The comparison will be restricted to database scoped elements if the source is a database.

SQL Server user for SSDT was sa.

Thanks in advance

UPDATE: I fixed comparison warning by following query but main problem is exist yet:

USE [master]
GO

Grant VIEW ANY DEFINITION to [USERNAME];
GO

USE [DBNAME]
GO

GRANT CONNECT to [USERNAME]
GRANT VIEW DEFINITION TO [USERNAME]

How are you comparing them? Via a build definition? A release definition? What command are you executing to compare them? — Daniel Mann, Oct 22 '16 at 15:52
Then what does this have to do with TFS? It sounds like a Visual Studio or SSDT problem. — Daniel Mann, Oct 23 '16 at 16:34
What is your question? The error message you posted seems clear enough. — Martin Smith, Oct 23 '16 at 18:34
@MartinSmith Please notice to picture, It's tell me about some difference but there is no change between source and target — Imran Sh, Oct 24 '16 at 05:32
@DanielMann Yes this is a problem or maybe I'm on wrong way. I want to know how can I fix this issue — Imran Sh, Oct 24 '16 at 05:33

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

I had a similar problem. Though you don't have string literals, it might still be working checking:

What Schema Compare thinks the difference is - click the expand button to the left of 'Procedure'.
If you have 'Ignore whitespace' turned on in the comparison options.
If the line ending of your file are windows (CR LF) or unix (LF).
(Brute force solution) Use sqlpackage.exe to extract the .dacpac of both databases, rename as .zip, and compare the contents of the two model.xml's - there will be a difference in there somewhere.

edited May 23 '17 at 12:13

Community

1
1

answered Nov 23 '16 at 16:34

Taran

12,822
3
43
47

I would also recommend checking the file encoding on both copies. – David Tansey Nov 23 '16 at 16:41
Dear @Taran your answer is so helpful, I'm working on this and found some new details. Please let me to check better – Imran Sh Dec 03 '16 at 08:05

Imran Sh · Accepted Answer · 2018-03-03T14:07:45.783

0

Finally I solved this problem by drop and recreate object in MSSQLServer:

edited Mar 03 '18 at 14:07

answered Oct 24 '16 at 07:13

Imran Sh

1,623
4
27
50

SSDT & TFS: compare SQL Server database not working

2 Answers2