sql permissions: authentication

Showing posts with label authentication. Show all posts

Sunday, March 25, 2012

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...
>> 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
>|||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...
>> 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
>>
>|||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...
>> 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...
>> 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
>>
>>
>|||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...
>> 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...
>> 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
>>
>>
>

Default Database brought offline SQL Server Agent Job fails!!!

Hi All,

There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.

I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.

Any pointers ?

Have you tried tracing the connection attempt using SQL Profiler?

This is a SQL engine security issue - its nothing to do with SSIS.

-Jamie

|||

Jamie,

You were right this has nothing to do with SSIS indded, I was using a Proxy account and I tried resetting the password, and it seems to work now. Thanks in helping me look at the right area.

Wednesday, March 21, 2012

Default access for a login

Hi guys,
I create a new login; let's say "aaa" with authentication "SQL Server
Authentication" and no database access specified.
Now... I'm able to connect to my SQL Server 2000 using Query Analyzer with
this "aaa" login and read data/execute stored procedure even I didn't specif
y
any level access. For me looks strange, I was expecting to access a database
after I define a user and permisions using this login. Is this normal?
Thanks!Yes they can connect. In terms of what they can execute
where, that depends.
If a login isn't assigned a database, they will access
databases under the guest account. If guest is in the
database, they have access to the database and whatever
public permissions have been set as well as whatever has
been explicitly granted to the guest user in that database.
Guest always exists in master and tempdb - you can't delete
the account from those databases.
-Sue
On Tue, 27 Sep 2005 14:19:01 -0700, "Radu"
<Radu@.discussions.microsoft.com> wrote:

>Hi guys,
>I create a new login; let's say "aaa" with authentication "SQL Server
>Authentication" and no database access specified.
>Now... I'm able to connect to my SQL Server 2000 using Query Analyzer with
>this "aaa" login and read data/execute stored procedure even I didn't speci
fy
>any level access. For me looks strange, I was expecting to access a databas
e
>after I define a user and permisions using this login. Is this normal?
>
>Thanks!

Sunday, March 25, 2012

default database problem

default database problem

default database problem

Default Database brought offline SQL Server Agent Job fails!!!

Wednesday, March 21, 2012

Default access for a login

sql permissions

Blog Archive

About Me