Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with attaching databases to SQL Server Management Studio.

Posted on 2011-09-13
15
Medium Priority
?
395 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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