Visual Studio DB Comparison Tool Falls Over with - SQL01270: Cannot open database

I recently restored a database to my local d开发者_高级运维ev machine from one of my colleagues. I am logged in as admin. I have set the default database to the one I am trying to connect to. I have given the admin user db_owner permissions on Sql's User Mappings screen in SQL.

However when I run the sql comparison tool i get the following error:

SQL01270: Cannot open database "[ADMIN-PC.VirginActive]" requested by the login. The login failed. Login failed for user 'admin-PC\admin'

The sql log files show the following:

2011-01-31 10:49:20.28 Logon Error: 18456, Severity: 14, State: 38. 2011-01-31 10:49:20.28 Logon Login failed for user 'admin-PC\admin'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

I've looked around for "Error: 18456, Severity: 14, State: 38." on the googlemachine and the feedback I get is that the user cannot login to the box. However the user is the admin user, the same user who created the db.

Any ideas on how to get around this?

I just resolved the same sort of issue, but it wasn't the database -- it was the schema comparison file itself (the .SCMP).

Somehow my SCMP had gotten a fully-qualified [machine.domain\instance.database] representation of the database name in a few key places. I opened the file in Notepad++ and changed those to the simple name (just 'DW' in my case), and the problem was resolved immediately.

(The 'few key places' were: DatabaseName inside ConnectionBasedModelProvider, and the value of the TargetDatabaseName property.)

I had the same issue today. The fix that I found was that if you change the database owner of the database to another user (I used "sa", for instance) you will be able to successfully connect/make changes in the Visual Studio 2010 Database Schema Comparison.

To change the database owner, right-click on database name and go to files... your NT account is most likely listed there (mine was). Change that to "sa" or something appropriate and apply those changes and then try again. This worked for me.

This can still be an issue with file read/write-rights, try running all programs as administrator (if running vista/win7) and changing the security settings of the mdf-files.

Otherwise try to connect as a sql-user that you have created only for this purpose.