Error No 4064
Contents |
SERVER - Fix : Error: 4064 - Cannot open user default database. Login failed. Login failed for user November 4, 2008Pinal DaveSQL, SQL Server, SQL Tips and Tricks197 commentsI have received following question nearly 10 times in error 4064 in sql server last week though emails. Many users have received following error while connecting to
Mssql Error 4064
the database. This error happens when database is dropped for which is default for some of the database cannot open user default database error 4064 user. When user try to login and their default database is dropped following error shows up.Cannot open user default database. Login failed. Login failed for user ‘UserName'. (Microsoft SQL Server, Error: sql error 40 4064)The fix for this problem is very simple.Fix/Workaround/Solution:First click on Option>> Button of "Connect to Server" Prompt.Now change the connect to database to any existing database on your server like master or msdb.Now click on connect and it will successfully log you in. Once you login in the system run following statement for your username and it should fix your problem. Make sure
Cannot Open User Default Database After Restore
to replace [test] with your username and master with yourdatabase name.ALTER LOGIN [test] WITH DEFAULT_DATABASE = master GOWatch SQL in Sixty Seconds video to Resolve Connection Error:Reference : Pinal Dave (http://www.SQLAuthority.com) Tags: SQL Error Messages, SQL Scripts2Related Articles SQL SERVER - Get Date Time in Any Format - UDF - User Defined Functions August 14, 2008Pinal Dave SQL SERVER - Remove Duplicate Entry from Comma Delimited String - UDF January 15, 2009Pinal Dave SQL SERVER - The Self Join - Inner Join and Outer Join July 8, 2010Pinal Dave 197 comments. Leave new Dennis Caunce January 29, 2014 1:49 amI used this method to get back into my server, Thanks. However, now everything is running slower than molasses uphill. Any ideas?Reply Sameer February 4, 2014 3:21 pmGreat. It worked. Thanks a lotReply Quang February 14, 2014 1:18 pmThanks, worked for me.Reply Brad March 28, 2014 3:57 amThank you so much!!!! You saved me big time!Reply Vani April 8, 2014 2:26 pmWorked like a charm! Thanks a ton!Reply Reza white April 17, 2014 7:45 amThanks a LOT mate. you make m
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » Administering » Login failed for user - Microsoft SQL Server,... 24 posts,Page sql server cannot open database requested by the login. the login failed 1 of 3123»»» Login failed for user - Microsoft SQL Server, Error: 4064 Rate Topic cannot open user default database login failed visual studio 2010 Display Mode Topic Options Author Message paulc.byrumpaulc.byrum Posted Thursday, March 25, 2010 4:31 PM Grasshopper Group: General Forum Members Last Login: Tuesday, April
How To Change Default Database In Sql Server 2008
6, 2010 11:55 AM Points: 23, Visits: 116 Hello,When I click on properties of a database, users, tables, or any object within SQL Server Management Studio, I get this error:Cannot show requested dialog.Cannot open user default database. http://blog.sqlauthority.com/2008/11/04/sql-server-fix-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user/ Login failed.Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)I have set the default database to the master, by selecting Options on the Connect to Server dialog and ran this command: ALTER LOGIN 'PBTF\bob WITH DEFAULT_DATABASE = master.SQL Server says:Cannot alter the login 'PBTF\bob', because it does not exist or you do not have permission.'PBTF\bob' account is in a security group, which has been added to SQL Server for Server Access. 'PBTF\bob' and the security http://www.sqlservercentral.com/Forums/Topic890300-146-1.aspx group are not added to the database security. Security group has admin rights in SQL Server.Despite all this, I still experience the error. Any tips on how I can fix this?Thanks Post #890300 OTFOTF Posted Thursday, March 25, 2010 4:59 PM Old Hand Group: General Forum Members Last Login: Thursday, October 31, 2013 3:44 AM Points: 314, Visits: 4,128 Cannot show requested dialog.Cannot open user default database. Login failed.Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)I think the error is what is: cannot open your default database.Does the default database still exist?You can probably go in via sqlCmd and change your default database.sqlcmd -E -d masterthen issue your alter login statement. Post #890306 paulc.byrumpaulc.byrum Posted Thursday, March 25, 2010 5:32 PM Grasshopper Group: General Forum Members Last Login: Tuesday, April 6, 2010 11:55 AM Points: 23, Visits: 116 The default database is master and it still exists.I get this when running the commands:C:\>sqlcmd -E -d master1> ALTER LOGIN pbtf\bob WITH DEFAULT_DATABASE = master2> goMsg 170, Level 15, State 1, Server THOTH, Line 1Line 1: Incorrect syntax near 'LOGIN'.I am logging in as bob with Windows Authenication. As mentioned before, bob's accont is in the Active Directory security group at the SQL Server level. Post #890314 OTFOTF Posted Thursday, March 25, 2010 5:56 PM Old Hand Group: General Forum Members Last Login: Thursd
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 http://stackoverflow.com/questions/10010062/error-in-login-to-sql-server the company Business Learn 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 4.7 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 connect to cannot open 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.2k1369107 asked Apr 4 '12 at 11:21 Tavousi 2,577122954 add a comment| 3 Answers 3 active oldest votes up vote 18 down vote accepted cannot open user 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 926917 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 databas