Solved

Problem with attaching databases to SQL Server Management Studio.

Posted on 2011-09-13
15
335 Views
Last Modified: 2012-05-12
I start SQL Express. In the Object Explorer, I right-click "Databases" and click on the "Attach" option. This is the error I get:

TITLE: Microsoft SQL Server Management Studio------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:


Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

------------------------------
BUTTONS:

OK
------------------------------

I've tried running SSMS in Admin and in other user mode. I've tried putting the database files in the same DATA file that houses the system database files. I just can't get SSMS to connect to any of the databases I have created with this error in the way. Does anyone have a solution?
0
Comment
Question by:WhyDidntItWork
  • 8
  • 7
15 Comments
 
LVL 8

Expert Comment

by:venk_r
ID: 36532494
Try changing the owner to sa. May be its assigned to Null.

 EXEC DatabaseName..sp_changedbowner ‘sa’;
0
 

Author Comment

by:WhyDidntItWork
ID: 36532646
I'm sorry venk. I don't understand. I'm not familiar with SSMS and its functionality.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36533048
can you open the query window?If yes run the below command in the query window

 EXEC <DatabaseName>..sp_changedbowner ‘sa’;

The below command will change the owner of the database to 'sa' and then you can give a try.
0
 

Author Comment

by:WhyDidntItWork
ID: 36533364
I copied and ran the query and got the following error message.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.

I don't mean to sound ungrateful but I think the query is being applied against the system databases that are currently attached to SSMS. However, what I really want is to attach a small custom database to SSMS and create a backup.  It's just that I keep on gettng an error message when trying to attach the custom database.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36533494
ok.I see the reason now.Its nothing to do with the database security.Pardon me.
Whats the authentication on the server?Windows or mixed mode?
Try rebooting the server in case the credentials havent been applied.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36533501
If the above things still didnt work. And if its mixed mode authentication try connecting SSMS thru sa user and the password and see if that works.
0
 

Author Comment

by:WhyDidntItWork
ID: 36533711
Hi venk. I'm using sql express with windows authentication.

Not sure how to reboot a server on SSMS.

The .mdf file was created as part of a Visual Wed Developer website where I introduced users. The .mdf file doesn't have security measures in terms of user name or password so I should be able to attach easily to the database with SSMS. However, as soon as I click "attach" I get the above error message.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:venk_r
ID: 36535665
I want you to do 2 things.
Please reboot the whole server if possible.
Open SSMS this time try connecting the server using user 'sa'  and check the permissions for your windows user login to make sure they have sysadmin priviledge.If its still not working use the sa login to complete your task.
0
 

Author Comment

by:WhyDidntItWork
ID: 36537696
Hi venk,

I discombooberated it, and reinstalled it last night.  I made sure to give sa rights to a windows adminstrator and to a normal windows user on the same computer. I still got the same error.

I followed your instructions as well. I right clicked the sql server in the object explorer and chose "resart" to "reboot" SSMS, both in the windows adminstrator and normal windows users. Got the same error.

I then considered your next step: connecting the server using the 'sa' user. Well, in order to do that, I need to set a password for 'sa'. So, in the object explorer, I drilled down on the security folder. I found the 'sa' icon. I right clicked 'properties'. To my surprise, even though I had reinstalled it just last night, the 'sa' password had been filled. Because I don't know what the 'sa' password, I tried changing the password but I got an error message stating I didn't have the authority to do so. Thus, I can't sign into SSMS as 'sa'. Does that make sense?

If not, I wonder how far my lap top can fly from a second storey window :).
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36537738
I think you need to change the authentication to sql server/mixed mode at his point. To do so follow the below steps.And give it a try

To change security authentication mode

1.In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

2.On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

3.In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

To restart SQL Server from SQL Server Management Studio
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL
Execute the following statements to enable the sa password and assign a password.

otherCopy
 ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

To enable the sa login by using Management Studio
1.In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

2.On the General page, you might have to create and confirm a password for the sa login.

3.On the Status page, in the Login section, click Enabled, and then click OK.

0
 

Author Comment

by:WhyDidntItWork
ID: 36537990
Hi venk,

I tried to change the scurity authentcation mode in the windows administrator and windows users. At step 2, when I click OK to change the authentiation mode,  get Error: 22001 "Access is denied'.

Did I install sql server expess improperly?

I noticed that I have legacy sql server 2005 and 2008 but when I reinstalled 2008 R2, I specifically instructed the installation process to install 2008 R2 on a stand alone basis.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 36538026
Im not really sure at this time .The last bet would be to uninstall all the sql server versions and reinstall 2008 r2 and this time choose mixed authentication instead of windows authentication, which will lead you to create sa password.Remember the password you have entered.And once the installation is done connect to the sql server using the sa login and the password.
0
 

Author Comment

by:WhyDidntItWork
ID: 36538969
Do I even need the2005 and 2008 sql server programs? I don't even remember how they got on my lap top? Thanks.
0
 
LVL 8

Accepted Solution

by:
venk_r earned 500 total points
ID: 36539942
I dont think you need them .You uninstall them and reinstall 2008 r2 with mixed authentication.
0
 

Author Closing Comment

by:WhyDidntItWork
ID: 36540814
Thanks venk! It worked.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now