default database problem

I did a stupid thing yesterday: I had to detach a database, and forgot it
was the default for that login account (Windows authentication). After it
was detached, I was unable to connect to that server through enterprise
manager afterward with that login.
Once I realized my error, I found a way to reattach the database using query
analyzer and that worked great. Unfortunately, I still can't login with the
Windows admin account. I connected as a different user (SQL Server internal
account) and saw that the default database for the Windows admin account in
question is now blank. !!!
So it seems I'm in a catch-22. Can't login to fix my login. I can't get a
response from our support staff so I'm trying to figure this out myself. I
need to have this access restored asap. How do I recover from this
embarrassing blunder (technically, not emotionally... lol)?
Thanks,
Randall Arnold
> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great.
This indicates that you have some login that you can use, possibly with sysadmin privileges. Use
sp_defaultdb to change the default database for the messed up login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158249818.863918@.xnews001...
>I did a stupid thing yesterday: I had to detach a database, and forgot it was the default for that
>login account (Windows authentication). After it was detached, I was unable to connect to that
>server through enterprise manager afterward with that login.
> Once I realized my error, I found a way to reattach the database using query analyzer and that
> worked great. Unfortunately, I still can't login with the Windows admin account. I connected as
> a different user (SQL Server internal account) and saw that the default database for the Windows
> admin account in question is now blank. !!!
> So it seems I'm in a catch-22. Can't login to fix my login. I can't get a response from our
> support staff so I'm trying to figure this out myself. I need to have this access restored asap.
> How do I recover from this embarrassing blunder (technically, not emotionally... lol)?
> Thanks,
> Randall Arnold
>
|||The problem is, the only login I had with sysadmin privileges is the one I
can no longer use. I tried the same local login I mentioned below, running
sp_defaultdb in query analyzer, and got this error:
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
> This indicates that you have some login that you can use, possibly with
> sysadmin privileges. Use sp_defaultdb to change the default database for
> the messed up login.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158249818.863918@.xnews001...
>
|||So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes sense it cannot change
default database for somebody else.
If the only sysadmin you have left is the messed up login, use ISQL.EXE, which will allow you in
even though the default database doesn't exist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158254641.735397@.xnews001...
> The problem is, the only login I had with sysadmin privileges is the one I can no longer use. I
> tried the same local login I mentioned below, running sp_defaultdb in query analyzer, and got this
> error:
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23rfuV%23B2GHA.3372@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks.
There are other sysadmin logins, I just don't have access to them. I
finally got ahold of our IT guys who do have such access so hopefully they
can get it resolved for me. Thanks for your advice!
Randall
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
> So you tried sp_defaultdb using a login which isn't sysadmin? OK, makes
> sense it cannot change default database for somebody else.
> If the only sysadmin you have left is the messed up login, use ISQL.EXE,
> which will allow you in even though the default database doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:1158254641.735397@.xnews001...
>
|||Problem fixed by IT support, lesson learned, thanks again!
I gave one of my SQL accounts sysadmin privileges and made sure its default
database was master. ; )
Randall Arnold
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:1158259731.549347@.xnews001...
> Thanks.
> There are other sysadmin logins, I just don't have access to them. I
> finally got ahold of our IT guys who do have such access so hopefully they
> can get it resolved for me. Thanks for your advice!
> Randall
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uyVKisC2GHA.3476@.TK2MSFTNGP04.phx.gbl...
>