Cannot remove dbo rights from a user

In the login properties on the Database Access tab, a user's name says
dbo instead of his name. I do not want him to have dbo permissions. I
cannot drop the db_owner permissions that he has on the database
because I get the error "Error 15405: Cannot use the reserved user or
role name 'dbo'."

How do I remove these permissions from this user?MK (mhedblom@.libertysite.com) writes:
> In the login properties on the Database Access tab, a user's name says
> dbo instead of his name. I do not want him to have dbo permissions. I
> cannot drop the db_owner permissions that he has on the database
> because I get the error "Error 15405: Cannot use the reserved user or
> role name 'dbo'."
> How do I remove these permissions from this user?

Obviously this person is the owner of the database. If you don't want
this, use sp_changedbowner to make someone else the DB owner.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Unfortunately it was not that obvious. The properties of the database
did NOT have him as the owner. It showed the owner as our system
account. However, I ran sp_changedbowner anyway just as a test and it
removed his alias'. Strange but thank you so much!

Erland Sommarskog wrote:
> MK (mhedblom@.libertysite.com) writes:
> > In the login properties on the Database Access tab, a user's name says
> > dbo instead of his name. I do not want him to have dbo permissions. I
> > cannot drop the db_owner permissions that he has on the database
> > because I get the error "Error 15405: Cannot use the reserved user or
> > role name 'dbo'."
> > How do I remove these permissions from this user?
> Obviously this person is the owner of the database. If you don't want
> this, use sp_changedbowner to make someone else the DB owner.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MK (mhedblom@.libertysite.com) writes:
> Unfortunately it was not that obvious. The properties of the database
> did NOT have him as the owner. It showed the owner as our system
> account. However, I ran sp_changedbowner anyway just as a test and it
> removed his alias'. Strange but thank you so much!

Have the database been restored/attached from a different server
instance?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Cannot remotely login to SQL Express since last windows update

Since today (after installing the latest Windows Updates !on my local machine!) I cannot login to a SQLExpress instance remotely; stating that it cannot find the server/instance.

The weird thing is: it only happens when I want to login to the non-default instance (sqlexpress 2005) (<IP>\<instance>).

When i'm using the default instance (msde 2000 or sqlexpress 2005 on another machine) (<IP>) it seems to work fine.

Does this have something to do with the windows updates, or is it just a coincedence?

Thanks in advance,

Remco RosThis is probably due to the update changed your setting. Just check your firewall on the server and put sqlbrowser.exe or UDP port 1434 to exception. Also make sure your sqlbrowser is running.|||we didn't install the updates on the server (yet) only on the client, so it must be a client thing.

Server:
Win 2003 / SQL Express 2005
Tcp/ip / named pipes: yes
sqlbrowser running
firewall exception on 1434udp 1433tcp.

tested on these client(s):
Local (without latest updates): CAN connect
XP Home with SQL Server Management studio (with lates windows updates): CANNOT connect
XP Pro with SQL Server Management studio Express (with lates windows updates): CANNOT connect
Win 2003 with SQL Server Management studio Express

(WITHOUT lates windows updates): CAN connect.

I see a pattern here...|||Are you sure your SQL Express listens on TCP port 1433 since your put 1433 in exception? What's the result if you run "telnet yourserver sql_tcp_port"? Can it connect? Could it be possible that IPSEC blocked the traffic between the problem machine if the IPSEC is on?

Cannot open user defualt database. Login failed.

Hi,

I know that there have been numerous posts on this topic, however none of the suggestions seems to resolve my problem. I am running a ASP 2.0 web application locally using SQL server 2005 express, but after using SQL Server Management Studio to browse the database of the application i get the following error everytime i log into the website:

Cannot open user default database. Login failed. Login failed for user '<<machine name>>\ASPNET'

All i had done is attach then detatch the database in SSMS and it seems to have changed the access permissions somehow.
I have ensured that the ASPNET user has full permissions (server roles and User mapping) in SSMS and i have granted the ASPNET user full rights to the ASPNETDB.MDF and log file, but i still get the same error.

Its really urgent i sort this problem out as soon as possible, so if anyone has tackled this problem before please help. Ideally i don't want to have to reinstall sql server, but if that is the only option and it will definitely fix the problem then i will.

Thanks,

Jack

Hey,

No, it didn't change permissions. It probably set the ASPNET account to that database, and now that database is detached and gone. With an admin account, go in, and change the default database for the ASPNET account.

|||

Thanks for your quick reply.

Currently when i log into SSMS i use windows authentication, and am an administrator on the system. So do you mean that when i go into SSMS that i need to attach ASPNETDB.mdf and then change the default database for the ASPNET account underSecurity > Logins. Then detach the db again?

I'm pretty new to sql server express, so if you could walk me through the steps i'd really appreciate it.

Thanks again.

|||

Hey,

Actually, that sounds about right. You know more than you think.

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?

Hi All,
I have not been using my SQL server 2000 for a while on this particular
machine. I had my account disabled and just got it re-enabled at work.
Login name is the same and password has changed as it does every 30 days. I
am trying to get into my server but keep getting the following error
message:
"SQL Server registration Failed because of the connection failure displayed
below. Do you wish to Register anyway?"
"Cannot open user default database. Login Failed"
I am at a loss and if anyone could suggest something to me other than
un-installing the instance and re-installing it, I would be appreciative.
Thanks,
JohnSeems like your account doesnt´have access to the default database, just try
to EXEC sp_grantdbaccess
with an administrative account for this user.
HTH, Jens Süßmeyer.
"John Rugo" <jwrugo@.patmedia.net> schrieb im Newsbeitrag
news:ObRFOWIfDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I have not been using my SQL server 2000 for a while on this particular
> machine. I had my account disabled and just got it re-enabled at work.
> Login name is the same and password has changed as it does every 30 days.
I
> am trying to get into my server but keep getting the following error
> message:
> "SQL Server registration Failed because of the connection failure
displayed
> below. Do you wish to Register anyway?"
> "Cannot open user default database. Login Failed"
> I am at a loss and if anyone could suggest something to me other than
> un-installing the instance and re-installing it, I would be appreciative.
> Thanks,
> John
>|||John,
See if this helps...
'Locked Out of SQL Server - Q. I locked myself out of SQL Server without
being able to log on with a sysadmin login. Can I fix this, or do I need to
reinstall SQL Server'
http://www.microsoft.com/sql/techinfo/tips/administration/May3.asp
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Rugo" <jwrugo@.patmedia.net> wrote in message
news:ehaGFkIfDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Unfortunately I can't get in as sa or any other login account using osql
or
> enterpise mgr or sql analyzer. I think I will have to uninstall the
> instance and re-install it. the odd thing is that SA doesn't even work.
> Thanks for you help.
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:OfsbMfIfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > Seems like your account doesnt´have access to the default database, just
> try
> > to EXEC sp_grantdbaccess
> > with an administrative account for this user.
> >
> > HTH, Jens Süßmeyer.
> >
> > "John Rugo" <jwrugo@.patmedia.net> schrieb im Newsbeitrag
> > news:ObRFOWIfDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > > Hi All,
> > >
> > > I have not been using my SQL server 2000 for a while on this
particular
> > > machine. I had my account disabled and just got it re-enabled at
work.
> > > Login name is the same and password has changed as it does every 30
> days.
> > I
> > > am trying to get into my server but keep getting the following error
> > > message:
> > >
> > > "SQL Server registration Failed because of the connection failure
> > displayed
> > > below. Do you wish to Register anyway?"
> > >
> > > "Cannot open user default database. Login Failed"
> > >
> > > I am at a loss and if anyone could suggest something to me other than
> > > un-installing the instance and re-installing it, I would be
> appreciative.
> > >
> > > Thanks,
> > > John
> > >
> > >
> >
> >
>

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. Login failed. (only when IDE is open)

I get the below error only when my IDE open. It connects well when it is found closed.

[SqlException (0x80131904): Cannot open user default database. Login failed.
Login failed for user 'JP\ASPNET'.]

I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab

But I wish someone could give me the proper solution.

I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.

Environment: XP pro, VWD and SQL Express

The problem i think is that u-r user that u r using has default database and that dont exist, to correct that u must go in the property of that user in Sql menagment system and set other default databse for that user..

I hope this will help

Sorry for my bad english

|||

I believe that when you have the database opened as "user instance" it can not be opened by more than one application, like Visual Studio and SQL Server Management Express, etc.

|||

Hi jpbiznes,

Could you please explain a little more?

where did you get that error message? From VWD or from IIS ? I guess it is from IIS since if you are using VWD accessing database, the account should be the one which you use to run VWD -- in this case the account won't be ASPNET.

I get the below error only when my IDE open.

There is a possibility that if you have attached your database file from one process, you cannot attach it again from another process. Since one physical database file cannot be attached by two different instances (note we use customer instance in sql2005 express). But in that case the error message should look lik: cannot attach database again since database file is currently in use.

Also, I performed a search through internet and find this KB article which may be helpful to you:

SYMPTOMS

loadTOCNode(1, 'symptoms');

Each user has a default database. When you connect to computer that is running Microsoft SQL Server, and you do not specify a login database, the default database is used. However, if the default database is unavailable at the time of the connection, you may not be able to connect. Instead, you receive error message 4062 or error message 4064. The text of the error messages is as follows:

Cannot open user default database

CAUSE

loadTOCNode(1, 'cause');

The user default database is unavailable at the time of connection. It is possible that the database:

?Is in suspect mode.?No longer exists.?Is in single user mode and the only available connection is already being used by someone else or by something else.?Has been detached.?Has been set to the RESTRICTED_USER state.?Is offline.?Is set to emergency status.?Does not have the login account mapped to a user or the user has been denied access.?Is part of a database mirror.

Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.

http://support.microsoft.com/?id=307864

Hope my suggestion helps

Cannot open user default database. Login failed.

Hi,

I got this application system having SQLExpress as database (ABCSystem.mdf). I'm connecting to the database thru connection string specifying database path in App.config.

At the same time, I got two paths for the mdf files. The first one is the path for my database project while the second one is the path to conduct testing. (This means that I got identical ABCSystem.mdf in two paths)

1) C:\Program Files\Company ABC\ABCDatabaseProject\

2) C:\Program Files\Company ABC Limited\ABCSystem\Database\

In my development machine, I can run my application where database path is testing path (number 2).

When I tried to copy the ABCSystem.mdf and ABCSystem_log.LDF and my application program files to another PC to test, I got the subject error message. (note that I copy program files under directory "C:\Program Files\Company ABC Limited\ABCSystem\" and mdf files under "C:\Program Files\Company ABC Limited\ABCSystem\Database\")

I try to edit path from database testing path to database project path in app.config, run the application, and notice that it is running OK.

What seems to cause the problem? Coz I need the database in the testing path which is "C:\Program Files\Company ABC Limited\ABCSystem\Database\" AND NOT "C:\Program Files\Company ABC\ABCDatabaseProject\".

Thanks alot for your kind attention.

Try this first, and then check your string accordingly:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

Buck Woody

|||I'm having this identical issue with an ODBC connection.
After wasting hours, I realized I only get the error when I try to change database name. (e.g. from a development to a production server). Based upon everything I've tried, I determined you CANNOT change server name in a Crystal report programmatically as in the Crystal sample code. It may be a possible to change servers if using a command based report (explicit SQL query), but our reports all use tables so a trial would be irrelevant.

Cannot open user default database. Login failed.

Cannot open user default database. Login failed.
Login failed for user '[machine name]\ASPNET'.

I'm working in VS 2005 with a starter kit. I close VS and open an MDF file in SQL Server 2005 Management Studio to modify a table. I attach the file, change a data type, then detach the file. When I go back to VS and try to run the program, I get this error. Everything runs normally before I bring the file into Management Studio. It is not a permissions issue. Does SQL Server put some kind of lock on the file? This must be a fairly common problem. A developer will sometimes want to modify an .mdf file. How does he do that and then run his program? Thank you for any suggestions I can try.

What authentication is this instance. Post your connection string... this seems to be the problem with ASPNET user. The "ASPNET" user account is created in Windows XP by Microsoft .NET Framework. In the connection string you use a SQL Server Login and try.

From the link mentioned below :-

Applications that use SQL Authentication to connect to a database are not generally affected by using the ASPNET account. Applications that use integrated authentication and impersonation are also not generally affected. However, if an application is not impersonating and is using Integrated Authentication, you must grant access to the database for the ASPNET account.

You cannot use the ASPNET account when you try to authenticate to Microsoft SQL Server by using Integrated Authentication over named pipes. However, you can use the ASPNET account together with Integrated Authentication over the Transmission Control Protocol (TCP) transport.

If an application must use a Microsoft Access database, the ASPNET account must be able to write to the database file. Administrators must change the file permissions to allow this use.

Refer :

http://support.microsoft.com/kb/895967/

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=214866&SiteID=1

Madhu

|||

Hi

Seen that before.

Someone has modified your login or the default database is unavailable.

You can use the following command to log on using Query Analyser. Run it the in the RUN window.

Code Snippet

isqlw -S servername -E (-U username -P password if using sql login) -d master

This will log you on to master database and then you can check for any problems or reset the default database using sp_defaultdb.

hope that helps

Jag

Cannot open user default database. Login failed.

Hi,

I got this application system having SQLExpress as database (ABCSystem.mdf). I'm connecting to the database thru connection string specifying database path in App.config.

At the same time, I got two paths for the mdf files. The first one is the path for my database project while the second one is the path to conduct testing. (This means that I got identical ABCSystem.mdf in two paths)

1) C:\Program Files\Company ABC\ABCDatabaseProject\

2) C:\Program Files\Company ABC Limited\ABCSystem\Database\

In my development machine, I can run my application where database path is testing path (number 2).

When I tried to copy the ABCSystem.mdf and ABCSystem_log.LDF and my application program files to another PC to test, I got the subject error message. (note that I copy program files under directory "C:\Program Files\Company ABC Limited\ABCSystem\" and mdf files under "C:\Program Files\Company ABC Limited\ABCSystem\Database\")

I try to edit path from database testing path to database project path in app.config, run the application, and notice that it is running OK.

What seems to cause the problem? Coz I need the database in the testing path which is "C:\Program Files\Company ABC Limited\ABCSystem\Database\" AND NOT "C:\Program Files\Company ABC\ABCDatabaseProject\".

Thanks alot for your kind attention.

Try this first, and then check your string accordingly:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

Buck Woody

|||I'm having this identical issue with an ODBC connection.
After wasting hours, I realized I only get the error when I try to change database name. (e.g. from a development to a production server). Based upon everything I've tried, I determined you CANNOT change server name in a Crystal report programmatically as in the Crystal sample code. It may be a possible to change servers if using a command based report (explicit SQL query), but our reports all use tables so a trial would be irrelevant.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot Open User Default Database. Login Failed

Yesterday was not my best day. I disconnected my database to make a copy of it and forgot to reconnect it before closing SQL Server 2000.

Now when I try ot access the database I get message.

Cannot Open User Default Database. Login Failed

Is there a way to attach the database again or a work around for this?

Thanks in advance for your help!You should be able to get an admin to change your default database to pubs, or northwind. Then you would be able to log on, anyway. I am not sure if you would be able to re-attach the database without an admin, though.|||Use the osql.exe utility to run sp_defaultdb and change the default database.sql

Cannot open user default database, login fails

hi:
I got the problem, which lots of people might encounter before.

In my case, I use login control and sqlexpress to build a login page.
when password & user name were entered. It gave me:
"Cannot open user default database, login fails"

The solution given by msdn is:

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1.Click Start, click Run, type cmd, and then press ENTER. 2.Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

?If

the SQL Server login uses Microsoft Windows authentication to connect

to the instance, type the following at the command prompt, and then

press ENTER:

sqlcmd –E -S InstanceName –d master

?If

the SQL Server login uses SQL Server authentication to connect to the

instance, type the following at the command prompt, and then press

ENTER:

sqlcmd -S InstanceName -d master -U SQLLogin -P Password

Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.3.At the sqlcmd prompt, type the following, and then press ENTER:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.4.At the sqlcmd prompt, type GO, and then press ENTER.

I wonder what are InstanceName, SQLLogin & AvailDBName. It would be nice if someone can give me a specific example, since I am not sure how to type them in a command line. :)

You're in the wrong forum. Try here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

-Jamie

Cannot open user default Database login failed

Dear all,

I got the error message:

Error 27052 Could not connect to Microsoft SQL Server 'VPC' Cannot open User default Database login failed.

This error is happened when installing one application to Virtual Machine (Machine name: VPC).

Does anyone knows how to solve this problem? I checked from www.microsoft.com but i just got the limited information:

27502

The parameter is not valid.

Not applicable

SSCE_M_CMN_INVALIDPARAMETER

There is no further information.

Thanks & Regards,

Agus Suryadi

When you log in to SQL Server, SQL Server figures out who you are and then puts your session into a specific database. The database it puts you into is either the one you specified on your connection string (if you specified one) or into your default database, the database that your login has as its default.

If you can get into the SQL Server you could check the login you were connecting with to see what its default database is, then check to see if a database with that name currently exists.

Perhaps the application that is being installed expects a certain database to already be created, but apparently it's not there (or the Login/user doesn't have permissions to access it).

Don

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