ansaurus

Question

Can't log in to SQL Server 2008 Management Studio

Answer 1

+1 A:

When connecting from Management studio, In the "connect to server" dialog box, before connecting, click "Options" and change the "Connect to database" dialog box to "master".

Nestor 2009-12-02 23:54:04

That gets me in to the Object Browser, but anything else I try to do gets me the same message: "Cannot open user default database". Can't open my login to change the default db...

Number8 2009-12-03 00:24:56

Oh... Modify the "default database" in the login. Expand "Security", "Logins", Right click on your login and choose Properties. You'll see the "default database" in the "General" tab.

Nestor 2009-12-03 01:02:21

Tried that. Result:Cannot show requested dialog.------------------------------ADDITIONAL INFORMATION:Cannot show requested dialog. (SqlMgmt)------------------------------Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)------------------------------Failed to connect to server (local). (Microsoft.SqlServer.ConnectionInfo)------------------------------Cannot open user default database. Login failed.Login failed for user 'svr\usr'. (Microsoft SQL Server, Error: 4064)

Number8 2009-12-03 01:16:11

This is farkled. When I try to attach another db, I get this:TITLE: Microsoft SQL Server Management Studio------------------------------Cannot show requested dialog.------------------------------ADDITIONAL INFORMATION:Parameter name: nColIndexActual value was -1. (Microsoft.SqlServer.GridControl)

Number8 2009-12-03 01:19:38

oh boy... If you are using "Windows Authentication", open Management studio as an admin and then try to do what I said before...

Nestor 2009-12-03 01:21:06

No joy. Right-click SQL Svr Mgmt Studio, Run as Administrator.Same result when trying to modify svr\usr account.Same result trying to modify db1.With Windows Auth, can't change user name (why not?).

Number8 2009-12-03 01:38:07

Answer 2

A:

Login as an administrator and change your default database: ALTER LOGIN ... WITH DEFAULT_DATABASE = .... If you're not an administrator, ask an administrator to do it. If you must log in in the database in question, ask an administrator to investigate whi the database is offline.

Remus Rusanu 2009-12-03 00:58:57

All that would be nice if there were an administrator to ask.Logging in as administrator would be easy, if there were an admin account other than my login.I am running SQL Server 2008 on my laptop. All dbs are local. I believe the problem started when a .mdf, attached in SQL Server, got corrupted. Since it was the default db for my login, I can't log in.

Number8 2009-12-03 01:03:59

I tried doing this with osql:>osql -E -q "alter login svr\usr with DEFAULT_DATABASE=db1"Login failed for user 'svr\usr'.Cannot open user default database. Login failed.

Number8 2009-12-03 01:09:55

Tried sqlcmd, too:>sqlcmd -E -d master1> ALTER LOGIN SVR\USR WITH DEFAULT_DATABASE=MASTER2> GOMsg 102, Level 15, State 1, Server SVR, Line 1Incorrect syntax near '\'.1>

Number8 2009-12-03 03:11:32

Stop the SQL Server, restart it in administrative single user mode (`sqlservr -m -sMSSQLSERVER`), the log in as a laptop administrator, fix your login, restart the SQL.

Remus Rusanu 2009-12-03 03:19:58

Answer 3

+1 A:

The easiest way to do this is via command line. You keep opening connections using SSMS that fail...

Using command line you'll have one connection and delimit the login name correctly: "[SVR\USR]"

sqlcmd -S InstanceName -d master -E -Q"ALTER LOGIN [SVR\USR] WITH DEFAULT_DATABASE = master"

or replace -E with -U SQLLogin -P Password if you have a SQL login handy

gbn 2009-12-03 06:13:19

Thank you. Specifying the server\user correctly seems to have been the key.

Number8 2009-12-03 08:23:20

ansaurus

tags:

views:

answers:

Can't log in to SQL Server 2008 Management Studio

related questions