Mssql Error Number 4064
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack cannot open user default database login failed sql server 2008 Overflow the company Business Learn more about hiring developers or posting ads with us Stack
Sql Error 40
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community how to change default database in sql server 2008 of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error in login to SQL Server up vote 9 down vote favorite 2 When I want to sql server cannot open database requested by the login. the login failed connect to SQL Server 2008, I get this message: Cannot connect to server. Additional Information: Cannot open user default database. Login failed. Login fail for user 'sa'. (Microsoft SQL Server. Error:4064) How do I resolve this error? sql-server-2008 login share|improve this question edited Aug 19 '12 at 9:46 Peter Mortensen 10.3k1369107 asked Apr 4 '12 at 11:21 Tavousi 2,592122954 add a comment| 3 Answers 3 active oldest votes up vote 18
Cannot Open User Default Database After Restore
down vote accepted This error (Microsoft SQL Server.Error:4064) occurs when the database which is dropped is default for some database user. When the user tries to login and their default database is dropped the error shows up. Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064) The way to resolve this is given here, summarized below. The solution to log on is: From the Connect to Server dialog, click Options Change value of Connect to database: to any valid database on the server The permanent solution to fix the server is to run SQL like: ALTER LOGIN [test] WITH DEFAULT_DATABASE = master GO share|improve this answer edited Oct 24 '14 at 19:00 Abacus 946917 answered Apr 4 '12 at 11:52 ta-run 4,01912046 2 This error will occur whenever the user's default database is offline as well. –Morv May 27 '13 at 16:00 add a comment| up vote 0 down vote In my case this was due to a user being a member of multiple groups, each group having its own login. All groups' logins need to have their default database set to a database accessible to the user. Other possible problems are listed at https://support.microsoft.com/en-us/kb/307864. You can set each login with an invalid
Get Your Own Blog If you would like to blog on SQLServerCentral.com then send an email to webmaster@sqlservercentral.com. Contact the author Please log in or register to contact the author of this cannot open user default database login failed visual studio 2010 blog All Blogs All Bloggers on SQL Server Central Feeds Subscribe to this blog Archives cannot open user default database. login failed sql 2014 for this blog February 2016 June 2014 February 2014 October 2013 May 2013 March 2013 February 2013 October 2012 August 2012 July 2012
Cannot Open User Default Database. Login Failed. Login Failed For User 'nt Authority System'
May 2012 April 2012 March 2012 January 2012 December 2011 November 2011 October 2011 September 2011 June 2011 April 2011 December 2010 November 2010 September 2010 July 2010 June 2010 May 2010 April 2010 March 2010 February http://stackoverflow.com/questions/10010062/error-in-login-to-sql-server 2010 January 2010 December 2009 August 2009 June 2009 May 2009 April 2009 March 2009 January 2009 December 2008 October 2008 September 2008 July 2008 Dukagjin Maloku - Dugi'DBA Everything that I can post for the SQL Server ... Fixing: SQL Server Error 4064 … Posted on 11 February 2013 Comments Briefcase Print An error that is known how to solve it, I was the victim today with it and when I http://www.sqlservercentral.com/blogs/dugi/2013/02/11/fixing-sql-server-error-4064-/ started the SSMS, I saw that error. I’m telling you here how to fix it as fast info and references from MS. This error usually comes when your DB no longer exists or it is in suspect mode or has been detached or offline etc, and you will have the pop-up message with error description and the number 4064: Ok, as you can see the user Blue cannot connect in the default database and the login failed! Fixing this error is really easy, so you can try again to login with that user but first before to Connect, you must go to the Options that is in right corner below and you will be asked for the Connection Properties on the second tab of the login dialog box and you must enter the master database in the drop-down box, see the fig below and hit Connect: Now you will be able to connect in your SQL Server. So what's next!? As I said, some of the reason why it happened are described above, so now you can check for the correct DB that must be as default DB of that user. After that to be sure for the next connection you need to change the default DB via T-SQL or GUI: GUI: Open the SSMS on the right, you have object Explore
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn http://stackoverflow.com/questions/10010062/error-in-login-to-sql-server more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, https://benharrell.wordpress.com/2007/01/15/cannot-open-user-default-database-login-failed-login-failed-for-user-username-microsoft-sql-server-error-4064/ helping each other. Join them; it only takes a minute: Sign up Error in login to SQL Server up vote 9 down vote favorite 2 When I want to connect to SQL Server 2008, I get this message: cannot open Cannot connect to server. Additional Information: Cannot open user default database. Login failed. Login fail for user 'sa'. (Microsoft SQL Server. Error:4064) How do I resolve this error? sql-server-2008 login share|improve this question edited Aug 19 '12 at 9:46 Peter Mortensen 10.3k1369107 asked Apr 4 '12 at 11:21 Tavousi 2,592122954 add a comment| 3 Answers 3 active oldest votes up vote 18 down vote accepted This error (Microsoft SQL Server.Error:4064) occurs when the database which is cannot open user dropped is default for some database user. When the user tries to login and their default database is dropped the error shows up. Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064) The way to resolve this is given here, summarized below. The solution to log on is: From the Connect to Server dialog, click Options Change value of Connect to database: to any valid database on the server The permanent solution to fix the server is to run SQL like: ALTER LOGIN [test] WITH DEFAULT_DATABASE = master GO share|improve this answer edited Oct 24 '14 at 19:00 Abacus 946917 answered Apr 4 '12 at 11:52 ta-run 4,01912046 2 This error will occur whenever the user's default database is offline as well. –Morv May 27 '13 at 16:00 add a comment| up vote 0 down vote In my case this was due to a user being a member of multiple groups, each group having its own login. All groups' logins need to have their default database set to a database accessible to the user. Other possible problems are listed at https://support.microsoft.com/en-us/kb/307864. You can set each login with an invalid default database to have a default database of master (accessible to all logins): alter login [loginname] with DEFAULT_DATABASE = master share|improve this answer answered Jun 23 '15 at 7:21
SQL SERVER, SQL2000, SQL2005, SQL2008, SQLDB -- benjamin harrell @ 8:28 pm Cannot open user default database. Login failed. Login failed for user ‘UserName'. (Microsoft SQL Server, Error: 4064) This error bugged the crap out of me tonight and I saw all kinds of crazy answers usually involving some sort of SQLCMD -blah -blah…i think this maybe the problem with command line happy developers/dbas but to make a long story short the fix is EASY and you will kick yourself for not trying this. 1. click the options button if the options are not showing. 2. click the connection properties tab if it is not active 3. click on the Connect to database: dropdown 4. type in the name of a database that still exists (ex. ‘master') 5. connect! I heard all kinds of rants and raves about how atrocious it was that microsoft did this to us and I agree it does suck that SQL Server doesn't do something about this automagically but what would you suggest it do? [UPDATE] I wanted to move some of the great ideas in the comments to the top so that people could find other solutions more quickly: From Commenter Fritz Laurel: ALTER LOGIN loginid WITH DEFAULT_DATABASE = master GO From commenter Das: For those of you who use their Domain accounts, here is the Alter statement to run once you assign the MASTER DB from the above step. ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master Replace [BUILTIN\Administrators] with your [domain\usr] NOTE:[Brackets] required on Domain\User accounts From commenter Breb02 (SQL 2000?) RUN–>cmd sqlcmd -E -d master (osql -E -d master for SQL 2000) 1>sp_defaultdb ‘Computer\username’, ‘master’ 2>go Later SQL Server versions: sqlcmd -E -d master From commenter Fuad Ahmed And the solution for me was just switching the registration property of the server from sql authentication to windows authentication. I think I was lucky that the login sa had different default database and the windows authentication had different. From commenter Tony G I’m using VS2008 Server Explorer. I ope