"cannot open user default database. Login failed"

When attempting to connect to my local instance of SQL server I am getting t
his error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
ThanksIt looks like you deleted the database that was the default database for
your login. One method to fix the problem is to connect using the OSQL
command-line utility, overriding the default database. You can then change
the default DB to one that exists.
The example below connects to the local server with SQL authentication and
changes the default database to 'master':
OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
,'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
When attempting to connect to my local instance of SQL server I am getting
this error message: "cannot open user default database. Login failed".
My OS is WinXp v2002 SP2. This issue started after I deleted a database on
my local server, prior to that it was working fine.
Thanks|||Dan,
Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
According to OSQL help: "If neither the -U or -P options are used, SQL
Server 2000 attempts to connect using Windows Authentication Mode." Well
that would be my network account & password and if I just enter OSQL -d
master and then my network account password at the prompt, it fails (login
failed for user). I know I'm correctly entering my current network account
password.
Any ideas?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
> It looks like you deleted the database that was the default database for
> your login. One method to fix the problem is to connect using the OSQL
> command-line utility, overriding the default database. You can then
> change the default DB to one that exists.
> The example below connects to the local server with SQL authentication and
> changes the default database to 'master':
> OSQL -d master -U MyLogin -P MyPassword -Q"EXEC sp_defaultdb 'MyLogin'
> ,'master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:u6aIYXRwFHA.3864@.TK2MSFTNGP12.phx.gbl...
> When attempting to connect to my local instance of SQL server I am getting
> this error message: "cannot open user default database. Login failed".
> My OS is WinXp v2002 SP2. This issue started after I deleted a database
> on my local server, prior to that it was working fine.
> Thanks
>|||My machines gives an error if I don't use -U or -E. Did you try using the -E
switch to login using
your Windows account?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL Ser
ver 2000 attempts to
> connect using Windows Authentication Mode." Well that would be my network
account & password and
> if I just enter OSQL -d master and then my network account password at the
prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Using the -E go me in & allowed me to change my default DB & resolve the
issue.
Thanks a million!! Have a good day.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vKLtxdwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> My machines gives an error if I don't use -U or -E. Did you try using
> the -E switch to login using your Windows account?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||The example I posted assumed a SQL login. Like Tibor mentioned, you can
specify '-E' to establish a trusted connection using your current Windows
account. You never need to specify a Windows account with the '-E'
parameter but you still need to specify the name of the account to
sp_defaultdb. For a local Windows account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
or for a domain account:
OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
If your SQL Server access is via membership of the local Administrators
group, you'll need to change the default database for that group instead:
OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"Brent Stevenson" <essexbs@.insightbb.com> wrote in message
news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Dan,
> Thanks for the reply! But infortunaley that fails with a "login failed for
> 'user' error."
> According to OSQL help: "If neither the -U or -P options are used, SQL
> Server 2000 attempts to connect using Windows Authentication Mode." Well
> that would be my network account & password and if I just enter OSQL -d
> master and then my network account password at the prompt, it fails (login
> failed for user). I know I'm correctly entering my current network account
> password.
> Any ideas?
> Thanks
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oex4GfRwFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||I just had the same problem, and used this same solution. Deleted a
database, didn't realise it was my default DB (or that it mattered), and
couldn't figure out the account credentials problem I was having using the
command line suggestions found elsewhere. Was using a domain admin account.
Thanks very much!
Bryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u9z7NBewFHA.2540@.TK2MSFTNGP09.phx.gbl...
> The example I posted assumed a SQL login. Like Tibor mentioned, you can
> specify '-E' to establish a trusted connection using your current Windows
> account. You never need to specify a Windows account with the '-E'
> parameter but you still need to specify the name of the account to
> sp_defaultdb. For a local Windows account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyMachine\MyAccount','master'"
> or for a domain account:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'MyDomain\MyAccount','master'"
> If your SQL Server access is via membership of the local Administrators
> group, you'll need to change the default database for that group instead:
> OSQL -d master -E -Q"EXEC sp_defaultdb 'BUILTIN\Administrators','master'"
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Brent Stevenson" <essexbs@.insightbb.com> wrote in message
> news:OCxkkudwFHA.1028@.TK2MSFTNGP12.phx.gbl...
>