cannot open default database

I have a website project setup in my development environment using Visual Studio 2008.  I have my database located in the App_Data folder.

Everything has been working fine.

Yesterday, I had to attach and then detach my database to Sql Server Express 2008 to run a command line utility to setup membership and roles...no big deal.

After I detached and then went back into Visual Studio and tried to open the database or one of my pages, I get the error:

Cannot open user default database. Login failed.
Login failed for user 'DEpps\Dwayne'.

Thiis is the first time I've encountered this error?  The default database is set to master.  I tried changing it to the database I'm using for this project, but still the same error.

The login I'm using is:

<machineName>\<loginName>

Did the attach and detach cause something to go awry?

Thanks for any help.
-Dman100-Software ConsultantAsked:
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Change the default database of your user to your database name

EXEC sp_defaultdb 'DEpps\Dwayne', 'ur_db_name'

PS: make sure you are logged in using privileged login to execute above statement
0
13598Commented:
Go into SQL2008. Under security check the logins. Find the user, right-click on it and open the properties. Make sure the user has the needed access to that particular db.
0
-Dman100-Software ConsultantAuthor Commented:
Hi rrjegan17 and 13598,

I tried each of your suggestions, but I'm getting the same error.  Any other suggestions I can try?

Thanks.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Have you created or associated your login to any of the databases in your Server..
If not, then delete this login and then re-create it and the go to User Mapping and choose the required databases and then try again..
0
-Dman100-Software ConsultantAuthor Commented:
Yes, I just tried that.  I had just done a clean install of my system and have only setup 4 databases that I needed immediately in my development environment.

Two of the databases work fine.  The other two work in SQL Server Express 2008, but when I try to run my apps in Visual Studio, I get the error.

Interestingly, both of the databases that are throwing the error were initially detached and worked fine from within the App_Data folder.  No problems....everything ran fine.

When I attached the databases to run aspnet_regsql.exe tool and then detached the databases is when I started getting the error message.  It makes me think something happened in the attach/detach process that caused this to happen.

I tried recreating the login and mappings.  I'm still getting the same error.

Very frustrating...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> It makes me think something happened in the attach/detach process that caused this to happen.

You might have missed logins during the Detach and Attach process..
Kindly drop out logins and associated users if not dropped properly and try recreating it again..
0
-Dman100-Software ConsultantAuthor Commented:
okay, before I drop the logins again, here is where I am.

I have my database attached in Sql Server Express 2008.  Under the security node, I have the following logins:

BUILTIN\Administrators
BUILTIN\Users
DEPPS\Dwayne
DEpps\SQLServer2005MSSQLUser$DEPPS$SQLExpress
NT AUTHORITY\SYSTEM
sa

Under the security node for the database, I have the following logins:
dbo
DEPPS\DWAYNE
guest
INFORMATION_SCHEMA
sys

What logins do I need to drop? All of them for both the database and under the primary security node?
0
13598Commented:
Did you check what access you have for the database.
On your login DEPPS\DWAYNE for example, right-click and clidk on Properties.
There click on user mapping. Is the database selected and what kind of access do you have?
It should be db_owner and public.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Under the security node for the database, I have the following logins:

Let me correct it out, If you find it under databases, then it was user and not login..
Create DEPPS\DWAYNE login in the server and run this script:

sp_change_users_login @Action='update_one', @UserNamePattern='DEPPS\DWAYNE',    @LoginName='DEPPS\DWAYNE';
0
13598Commented:
If he goes under security/logins/usermappings and the database is there but not selected all he needs to do is click on it to select it. And click on db_owner and public.
He doesn't really need to run the script.
0
-Dman100-Software ConsultantAuthor Commented:
I went under security/logins/usermappings.  The database is selected and the user is 'DEPPS\DWAYNE' and the default Schema is dbo.  The role membership is db_owner and public

I still got the same error.

I ran the script you indicated and got the following error:

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'DEPPS\DWAYNE' is absent or invalid.

This is the script I ran:
exec sp_change_users_login @Action='update_one',@userNamePattern='DEPPS\DWAYNE',@LoginName='DEPPS\DWAYNE'

The login exists under security/logins
and under the database/security/users

I'm thinking I should script out the database and then recreate?
0
13598Commented:
Try running this :
use yourdatabase
go
sp_dropuser 'DEPPS\DWAYNE'
use yourdatabase
go
sp_grantdbaccess 'DEPPS\DWAYNE'
go
sp_addrolemember 'db_downer', 'DEPPS\DWAYNE'
0
13598Commented:
sp_addrolemember 'db_owner', 'DEPPS\DWAYNE'

typo
0

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
-Dman100-Software ConsultantAuthor Commented:
I tried running the script you suggested:

use NORTHWND_MasterPages
go
sp_dropuser 'DEPPS\DWAYNE'
use NORTHWND_MasterPages
go
sp_grantdbaccess 'DEPPS\DWAYNE'
go
sp_addrolemember 'db_owner', 'DEPPS\DWAYNE'

I still get the same error??
0
-Dman100-Software ConsultantAuthor Commented:
oh, as a follow-up, as another problem I just encountered is when I try to attach a database.

I get the following error:

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 can run this script and it will attach, but I get the above error when I just go databases>>attach

sp_attach_db @dbname='NORTHWND_MasterPages',@filename1='c:\my documents\visual studio 2008\projects\Master Pages Tutorial\projects\web app\MasterPagesTutorial\App_Data\NORTHWND_MasterPages.mdf',@filename2='c:\my documents\visual studio 2008\projects\Master Pages Tutorial\projects\web app\MasterPagesTutorial\App_Data\NORTHWND_MasterPages_log.ldf'

This is the first time I've used Sql Server Express 2008.  I didn't have any problems with Sql Server Management Studio Express 2005

Should I revert back to the 2005 version?  Or, are there any patches I need to run that might be causing the issues I'm encountering?

Thanks for both of your help!  I appreciate it.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> This is the first time I've used Sql Server Express 2008.  I didn't have any problems with Sql Server Management Studio Express 2005

Apply SP1 for SQL Server 2008 and give it a try.
There should not be an issue with SQL Server 2008 with respect to attaching databases.
0
13598Commented:
That message indicates you have a 'bad' record, a record with a value of -1 or null in your primary key field. Can you look at the data and see? If you see it can you delete it or change the primary key to be a another number (unique) and then try detaching and reattaching again?
0
-Dman100-Software ConsultantAuthor Commented:
I finally resolved the problem, but I'm not quite sure why and exactly what corected the problem.  It had to do with permissions.

Here is what I did.

I went to the App_Data folder in my web project where the .mdf and .ldf files are located.  I copied them and went to paste the files onto my desktop.  I received the following message:

"You'll need to provide administrator permission to copy this file"  I clicked continue, then deleted the existing files from the App_Data folder and then simply copied the files from the desktop back into the App_Data folder and viola...it worked.  I could connect to the database and run the app.  No errors, no problems.

I took a look at the App_Data folder permissions and I did not see any different permissions.  Everything was the same.  So, what is this administrator permission message that came up?  I don't see anything different.  My login should have administrator permissions.  I installed the OS and configured my system.  Now, I just upgraded to Windows 7 Ultimate, so maybe this is something new in Windows 7 that I don't know about.

Any thoughts on this?

Thanks again for both your help.  I really do appreciate it.  I'm glad I finally got this resolved.
0
13598Commented:
Thank you for sharing your solution so others can benefit.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks -Dman100- for sharing the solution and a helpful member of EE..
0
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
.NET Programming

From novice to tech pro — start learning today.