Cannot open user default database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cannot open user default database

Author

Topic

obinna
Starting Member

26 Posts

Posted - 2008-01-15 : 08:52:37

Guys

I am currently getting the error Cannot open user default database. Login failed.
Login failed for user '<user name>'. (Microsoft SQL Server, Error: 4064)

I took a backup of my Production Database but when I try to restore it I get this error. I cant do anything at the moment without getting this error.

Can you please help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-15 : 09:06:17

it looks like your database username isn't mapped to a login

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

obinna
Starting Member

26 Posts

Posted - 2008-01-15 : 09:11:27

spirit1

How do I map it please

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-15 : 09:14:58

use sp_change_users_login sproc
http://technet.microsoft.com/en-us/library/ms174378.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

obinna
Starting Member

26 Posts

Posted - 2008-01-15 : 09:41:24

spirit1

Many thanks for your constant help, can you please give me an example, not too good with transact sql, do I create or amend an admin account or do I create an account from scratch for this.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-16 : 05:24:36

In the example, outlined above :
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-16 : 06:44:51

you got the problem because you dropped the default database which is set by you manually.

the solution for this is......
while connecting window asked to connect,.....there click on options.....
now you can find "Connection Properties" tab. there you find connect to database option.
there you can type master.
now you will be able to connect.

after that, you please run sp_defaultdb 'user_name','master'

there username should be your connecting name to the server(like sa).

that's it

Vinod
Even you learn 1%, Learn it with 100% confidence.

obinna
Starting Member

26 Posts

Posted - 2008-01-16 : 11:26:41

Sunsavin

Many thanks for this, ill do it now

Regards

O.

obinna
Starting Member

26 Posts

Posted - 2008-01-16 : 11:37:51

Sansavin

sOrry to be a pin, ive done exactly what u told me 2 do.
sp_defaultdb 'sa','master'

But still getting the error.

Regards

Obi

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 11:45:59

also see if this helps you
http://support.microsoft.com/kb/307864

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

obinna
Starting Member

26 Posts

Posted - 2008-01-16 : 12:12:23

Spirit1

sqlcmd –E -S InstanceName –d master - mate do u know what the instance name is.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 12:19:39

it's the name of your sql server instance.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-17 : 04:09:03

quote:
Originally posted by obinna

Sansavin

sOrry to be a pin, ive done exactly what u told me 2 do.
sp_defaultdb 'sa','master'

But still getting the error.

Regards

Obi

what is the error you are getting?

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources