My SQL DB is not available for selection, SQL Migration Assistant For Access

I want to migrate an Access table to my SQLDatabase 'JTSConverson' using SQL Server Migration Assistand For Access 32-bit.

I am going thru the steps with the Wizard.  I have selected the Access Table I want to bring over and now need to connect to the SQL database where I want to move the table.  Unfortunately my DB is not one of the selections in the drop down menu on the SQL Connect Dialog box shown in the attached word document.SqlConnectDialog.docx

I see my database when I am in Sql Server Management Studio.  I even have a 'User DSN' set up for it.

How do I make it appear as on option to migrate to, on the SQL Connect screen of the SSMA for Access?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerCommented:
Check the schema of your database. i guess its not dbo, and hence its not displaying in the drop down list
mlcktmguyAuthor Commented:
I am very new to SQL Server.  How do I check what the Schema is now and how do I change it if it's not dbo?
mlcktmguyAuthor Commented:
I did some more research and I think the schema is dbo.  All of the tables in the DB have the prefix dbo.
Any other reasons the DB wouldn't appear?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Just to double-check, when you connect via SSMS, are you using the same SQL Server instance (MIKE72-PC\WIN72SQLSERVER) and credentials (Windows Authentication) as you do when you are using the utility?

If yes, your login has sufficient rights for the database to show up when an application tries to enumerate through the list of available databases.

If no, can you please trying using the same credentials on the utility as you do when you login via the SSMS?
mlcktmguyAuthor Commented:
I am using the same SQL Server instance and Windows authorization to connect in SSMS.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
In this case, I think it's probably related to the schema settings on the target database. Does this help: http://blogs.msdn.com/b/ssma/archive/2011/05/25/how-to-migrate-to-a-schema-in-an-existing-database-using-ssma.aspx
mlcktmguyAuthor Commented:
"In this case, I think it's probably related to the schema settings on the target database. Does this help: http://blogs.msdn.com/b/ssma/archive/2011/05/25/how-to-migrate-to-a-schema-in-an-existing-database-using-ssma.aspx"

It is entirely possible that I am so new to SQL that your answers are on target and  just don't understand them.

The database has already been migrated and the schema is set to dbo.  I can see the DB and transfer the table when using the SQL Import Export Wizard 32-bit but this wizard doesn't retain the indexes.

Still, when I try to use SQL Server Migration Assistant this DB does not appear in the drop down list for selection.

In case this give any clarity to my issues here is a pic of what I see when logging into SQL Server Manager and the list of DB's in this SQL Server Instance.
SQL Display
As you can see, in the DB list there are two DB's, 'GRBSSMACreated01' and 'JTSConversion'.   As I mentioned in my very first post, when I open the SQL Server Migrations Assistant 'GRBSSMACreated01' is the only DB as an option to migrate data to, 'JTSConversion' is not shown as you can see in the second pic.
Sql Server Migration Assistant
mlcktmguyAuthor Commented:
Still no resolution to this one.  Any other ideas?
Vitor MontalvãoMSSQL Senior EngineerCommented:
That combo box is editable? If so, did you try to type the database name on it (JTSConversion)?
If still not working can you post the users from the two databases ('GRBSSMACreated01'  and 'JTSConversion') ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thank you Vitor:  Didn't realize it was editable.  I was able to type in JTSConversion and it pulled the table.  However, I got a message at the end that probably explains why JTSConversion wasn't appearing in the drop down but I'm not sure how to resolve it.

This is the message:

SQL Message
How do I resolve this?
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, looks like it only shows the databases that you're user is dbowner.
You'll need to connect to SQL Server instance using SSMS and run the following query there:
USE JTSConversion
GO
EXEC sp_changedbowner 'YourUserNameHere';

Open in new window

This will make your user the owner of the database and then you can try exporting again.
mlcktmguyAuthor Commented:
How do I determine what my username is?

Is there a way to show the current owner name?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be the login that you provide in Authentication fields (Mike72, I guess).
But you can confirm it in SSMS. Just run the following query:
SELECT SUSER_NAME()

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
When you know the login name, open the Logins folder and double-click on the right one and go to Mappings where you'll see the user name that he has in the JTSConversion database:
Logins.png
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.