Cannot open user default database. User login failed.

Hi,

I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...

We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.

The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.

I have checked for orphaned users and the user I am logging in with from the webpage is not listed.

So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.

Thanks for your help, appreciated.

James.Edit your odbc connection and check the default database drop down box...it's probably set to master...|||Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .|||Originally posted by JDMoore
Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .

What ODBC says if you are trying to test data source under this account?
If everything is fine with ODBC - check your application...|||ODBC says that the connection is fine.

The application has not been changed and has worked fine so I'm virtually certainly it's nothing to do with the program code. The code in question is simply opening a connection to the database.

As I say the error is sporadic presumably an ODBC connection string can either be right or wrong?|||Remotely, but possible, that the default database is in single user mode, and if you login successfully, it means that you're the first one to show up, while when you get an error, then someone's already there (that's to attempt to explain the sporadic nature of the error.) But to be proactive, open EM and go to that database (make sure to register the server with sysadmin privileges.) Check the Options tab of the properties to ensure that the database is not in single user mode. Also, go to Security folder, then to Logins and open properties for the account that the app uses to login and set the default database to master. This is to prevent the situation when a different database was renamed to the one that is specified now as default. And finally, speaking about renamed databases, can you check if it happened recently?|||You might also want to check the connection type in the ODBC. There are times when it doesn't matter whether your using Pipes or Ip but it will test just fine then throw sporadic error messages when actually using the application.

I would try going into the client config button (2nd screen in the ODBC) and if it's set to Pipes then make it IP (port 1433 is default but may have to be changed depending on network) or vice versa.|||Run sql profiler to see what logins are attempting to connect - I am curious to see if the problem is that the iuser account is failing to connect ...|||Thanks for all the suggestions. I am not in until Monday now but when I get in, I will try everything and then get back to you all.

Thanks for posting.|||As always it turned out to be pretty simple...

As I looked further through the code it turned out that the program was looking at this database and then looking at a database on a different server.

When I went to the other server it turned out that THIS database was in single-user mode and hence the sporadic nature of the problem was explained.

Thanks very much for everyone's contribution, probably wouldn't have stumbled over this for ages without your suggestions.

James.

Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

sql

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

sql

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IFEXISTS(SELECT*FROMsys.server_principalsWHEREname= N'sa')

DROPLOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

sql

Cannot open database requested by the login. The login failed.

I get that error.

I'm sure you see this question alot, but i'm dumfounded by it. Problem is that i've taken the code directly off the live website, yes i've changed the connection string...

Private _connStr As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=EFM;User ID=*****;password=******;Trusted_Connection=true"

and am using the database that was originally worked on...

Only thing that has changed is my new computer... had to reload everything... new VWD, new sql express, new sql servermanagement studio express... all semmingly working fine... ie i can see the database and edit it, i just cant connect to it through the localhost to test the program.

Any Ideas? why does this error come up... how can i fix it?

Thanks in advance

Cannot open database "EFM" requested by the login. The login failed.
Login failed for user 'GERTIE\IBM USER'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open database "EFM" requested by the login. The login failed.
Login failed for user 'GERTIE\IBM USER'.

Source Error:

Line 19: MyBase.New()
Line 20: myConn = New SqlConnection(_connStr)
Line 21: myConn.Open()
Line 22: End Sub
Line 23:

Source File: C:\Documents and Settings\IBM USER\Desktop\Entertainment\wwwroot\App_Code\cDB.vb Line: 21

Stack Trace:

[SqlException (0x80131904): Cannot open database "EFM" requested by the login. The login failed.
Login failed for user 'GERTIE\IBM USER'.]
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606
 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501
 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +510
 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
 System.Data.SqlClient.SqlConnection.Open() +160
 Datalayer.cDB..ctor() in C:\Documents and Settings\IBM USER\Desktop\Entertainment\wwwroot\App_Code\cDB.vb:21
 _Default.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\IBM USER\Desktop\Entertainment\wwwroot\Default.aspx.vb:29
 System.Web.UI.Control.OnLoad(EventArgs e) +80
 System.Web.UI.Control.LoadRecursive() +49
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745

Did you recreate the user in SQL? Is the system still named GERTIE? The user still exists in the new system?

Jeff

|||

the user exists in the database... no change from the proviously working version...

the system name should be irrelevant because of using localhost in the string? or is that not correct.

i don't understand what you mean by the user still exists in the new system. maybe this line of thought has something to do with it...

|||

been searching, and have never seen this issue solved...

i'm starting to get worried...

|||

Some one might be interested to know that i solved this all by myself in the end. It would appear to have somehing to do with IIS not being installed... each time this happens to someone it seems to be moving the systems accross onto a new pc... well it happens very often... I installed IIS, rebooted the pc and then unattached and then reattached the database

worked a charm!!!

Hope this helps someone some where... Oh an i want some points for this contribution!!!;)

|||

Just to be clear, IIS was installed. Otherwise you never would have received an ASP.NET error, since the file could never be processed.

Jeff

sql

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

|||Ditto. Fixed me too.

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

Cannot Install AdventureWorks database.

I downloaded AdventureWorksDB from the CodePlex website and tried to install it. When the install program tried to attach it to SQLEXPRESS, it gave the error (below). I've also tried attaching the database directly using the SQL Management Studio and got exactly the same error.

I'd never heard of having incompatible database versions for SQL Server before. Am I missing a patch for SQLEXPRESS or ?. Thanks.

=================================================================

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Server Name: HP-MEDIACENTER\SQLEXPRESS
Error Number: 948
Severity: 20
State: 1
Line Number: 1

Sounds like the one in Codepley had been attached to SQL Server 2008, you should post that as a bug to the appropiate persons.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I got the following response from Microsoft Press that solves the problem:

================================

Thank you for contacting Microsoft Press.

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click “SQL Server 2005 SP2a” in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

|||

I faced the same problem and your answer helped me too!

Thanks buddy!

|||

Same issue for me too. It's working now. Thanks very much for posting the answer.

|||Ditto. Fixed me too.

Cannot find stored procedure

I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
PPerhaps the owner of the SP is not what you expect? I have not used web_administrator, but perhaps it does not prefix SPs scripted with dbo., and so the SPs are owned by a different user.|||My Error, SP is case sensitive and somehow during the transfer of the site and DB one letter in SP name was changed to upper case. Buggered if I know how.
Have a great weekend Doug
Thanks for the help
Pete