Solved

Problem with attaching databases to SQL Server Management Studio.

Posted on 2011-09-13
15
355 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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