November 3, 2008 at 4:31 am
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?
November 3, 2008 at 7:53 am
The default database set up for your login is not working. Before clicking "Connect", choose a database like master in the options tab.
November 3, 2008 at 7:58 am
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
November 3, 2008 at 8:23 am
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>" 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]
April 22, 2010 at 12:39 am
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
April 22, 2010 at 1:23 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply