Cannot open user default database

  • On SQL Management Studio Express this error appears when i try to see the properties of a LOGIN

    TITLE: Microsoft SQL Server Management Studio Express

    ------------------------------

    Cannot show requested dialog.

    ------------------------------

    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------

    Failed to connect to server RUY\SQLEXPRESS. (Microsoft.SqlServer.Express.ConnectionInfo)

    ------------------------------

    Cannot open user default database. Login failed.

    Login failed for user 'RUY\Rui'. (Microsoft SQL Server, Error: 4064)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Can you help me figure what is wrong?

  • The default database set up for your login is not working. Before clicking "Connect", choose a database like master in the options tab.

  • I has resolved the issue by doing this steps:

    This error (Cannot Open User Default Database, Login Failed Microsoft SQL Server, Error 4064), occurs when you try to connect to a SQL Server 2005 server where the default database for your user has been dropped.  Unfortuntely this occurs in Microsoft SQL Server Studio too - which means you cannot use it to resolve the issue (ie change the default database assigned to your login).

     

    However you can resolve this using ye-olde command prompt...

     

    1) At the command prompt enter:

      sqlcmd -E -d master

    This command  logs you onto the db server using specifying master as your default db.

    2) Within the SQL command prompt enter:

      alter login [MYDOMAIN\UserName] with default_database = master

    This changes the default database setting against your login.

    3) Then enter:

      go

    This executes the command.

    4) Then enter:

      quit

    This quit's you out of the sqlcmd tool/prompt.

     

    You screen will look something like:

    C:\>sqlcmd -E -d master

    1> alter login [MYDOMAIN\UserName] with default_database = master

    2> go

    3> quit

  • Actually you can address it with Management Studio also. Start a new connect, bring up the connection Login dialog. Click the "Options" button at the bottom and then change the Connect to database entry from "<default&gt" to "master".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I am connecting to SQL 2000

    I had by chance deleted the default database for my login id. But now when I try to login , I get the error

    ) At the command prompt I entered

    sqlcmd -E -d master

    This command logs you onto the db server using specifying master as your default db.

    2) Within the SQL command prompt enter:

    alter login [MYDOMAIN\UserName] with default_database = master

    This changes the default database setting against your login.

    3) Then enter:

    go

    After I enter the GO statement in the 3rd step , I get the message Msg 170, Level 15, State 1, Server , Line 1

    Line 1: Incorrect syntax near 'login'..

    Please help

  • Problem is solved.

    Since I was connecting to SQL 2000 database and ALTER LOGIN command does not exist in SQL 2000 and exists in 2005 onwards, my commands were failing. Instead I used the command

    Exec sp_defaultdb @loginame='login', @defdb='master' and it worked fine for me.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply