schema compare problem

[gerry]

Visual Studio 2008 sp1 Developer & Database editions , .NET 3.5 sp1

I created a "Database Project" project that references 2 local sqlserver
express 2005 database files

When I try to run a schema compare in Visual Studio ( Data > Schema Compare

New Schema Comparison ) I get the following error :

Schema information could not be retrieved because of the following error
:
Cannot open the database "C:\....\db.mdf" requested by the login. The
login failed.
Login failed for user MYDOMAIN\MyLogin

I also tried creating a new "SQL Server 2005 Wizard" project and it fails
with the same error when attempting to do the initial schema import.

Using different SqlServer express database files makes no difference - same
error.

Server Explorer in Visual Studio has no problem accessing these databases ,
SqlServer Managerment Studio Express has no problem accessing these
databases ,
SqlServer Managerment Studio Express shows that my login has full access to
both databases.

Why am I getting login errors doing a schema compare even though I have full
access to both databases and do not have this issue using the same login in
other apps.

Gerry

 

[Colbert Zhou [MSFT]]

Hello Gerry,

Does the problem occur when you click the Data->Schema Compare-> New Schema
Comparison menuitem or click the OK button of the New Schema Comparison?

Based on my understanding, we are trying to connect to local .MDF files
directly, right? Does the user has access right to the mdf host
folder(C:\... in your case)?

If we attach the .MDF file to SQL Server Express, and consume the database
from the SQL Server Express, do we still encounter this error?


Best regards,
Colbert Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

 

[gerry]

Hi Colbert,

thanks for the reply - answers below

additional info - I checked the event log just to see if anything was
showing up there and I found an application event being logged :
Source : MSSQL$SQLEXPRESS
Type : Failure Audit
Category : 4
Event ID : 18456

Description : Login failed for 'MYDOMAIN\gerry'. [CLIENT: <local machine>]
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 11 00 00 00 47 00 4d 00 ....M.Y.
0010: 53 00 30 00 38 00 5c 00 L.A.P.\.
0018: 53 00 51 00 4c 00 45 00 S.Q.L.E.
0020: 58 00 50 00 52 00 45 00 X.P.R.E.
0028: 53 00 53 00 00 00 07 00 S.S.....
0030: 00 00 6d 00 61 00 73 00 ..m.a.s.
0038: 74 00 65 00 72 00 00 00 t.e.r...

Noticing that this references the 'master' database I went looking at
permissions on that, under 'Security > Logins > MYDOMAIN\gerry > User
Mapping' I found that there was no mapping for the system databases. I
mapped the system databases setting database role membership to db_owner.

Also under 'Server Roles' this login does include sysadmin.

I restarted SqlServerExpress - no effect - still the same problem

Hello Gerry,

Does the problem occur when you click the Data->Schema Compare-> New
Schema
Comparison menuitem or click the OK button of the New Schema Comparison?

When I click ok , after selecting the databases to compare , the empty
SchemaCompare tab is displayed and then the error message pops up.

Based on my understanding, we are trying to connect to local .MDF files
directly, right? Does the user has access right to the mdf host
folder(C:\... in your case)?

Yes, the user is me and I am an admin on this machine ( no lectures please )
with full access at every level - I created the folder and the file and can
see in Sql Server Management Studio Express that I have full rights to the
sql express server.

If we attach the .MDF file to SQL Server Express, and consume the database
from the SQL Server Express, do we still encounter this error?

Assuming you are referring to attaching the database from Sql Server
Management Studio Express - No.

 

[Colbert Zhou [MSFT]]

Hello,

This issue seems to result from the "User Instance = True" property.

In the New Schema Comparison dialog, we can edit connection before we click
OK button to perform the comparsion. So, after you choose the database,
please click the edit connection button, and click the Advanced button,
find the User Instance property and set it to False. This should fix the
issue.

Please let me know if it resolves your issue. Have a nice weekend!

Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

 

[gerry]

that was it - now I need to dig into the ramifications of having or not
having this option in the connection string