Hi,
1) What's difference between disabling a login and denying it?
2) What does Denying mean for a login of type SQL Authentication?
Thanks in advance,
LeilaDenying a login is done by an administrator to prevent the person from
logging into SQL Server. Disabling is done by SQL Server itself. This is
typically because someone tried to login with the password unsuccessfully
several times and the limit was exceeded.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Leila" <Leilas@.hotpop.com> wrote in message
news:eYmc6p5fHHA.4032@.TK2MSFTNGP02.phx.gbl...
Hi,
1) What's difference between disabling a login and denying it?
2) What does Denying mean for a login of type SQL Authentication?
Thanks in advance,
Leila|||Thanks Tom,
Then what's the checkbox in status tab of a login properties (Login is
locked out). Mine is gray (I use win XP)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23tEa0s5fHHA.588@.TK2MSFTNGP06.phx.gbl...
> Denying a login is done by an administrator to prevent the person from
> logging into SQL Server. Disabling is done by SQL Server itself. This is
> typically because someone tried to login with the password unsuccessfully
> several times and the limit was exceeded.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eYmc6p5fHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1) What's difference between disabling a login and denying it?
> 2) What does Denying mean for a login of type SQL Authentication?
> Thanks in advance,
> Leila
>|||Is it grey *and* checked or is it grey and *not* checked?
When SQL Server disables a login, you should be able to uncheck that box.
You're not allowed to check it, however.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Leila" <Leilas@.hotpop.com> wrote in message
news:e%23sbDL6fHHA.4032@.TK2MSFTNGP02.phx.gbl...
Thanks Tom,
Then what's the checkbox in status tab of a login properties (Login is
locked out). Mine is gray (I use win XP)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23tEa0s5fHHA.588@.TK2MSFTNGP06.phx.gbl...
> Denying a login is done by an administrator to prevent the person from
> logging into SQL Server. Disabling is done by SQL Server itself. This is
> typically because someone tried to login with the password unsuccessfully
> several times and the limit was exceeded.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eYmc6p5fHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1) What's difference between disabling a login and denying it?
> 2) What does Denying mean for a login of type SQL Authentication?
> Thanks in advance,
> Leila
>|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Denying a login is done by an administrator to prevent the person from
> logging into SQL Server. Disabling is done by SQL Server itself. This is
> typically because someone tried to login with the password unsuccessfully
> several times and the limit was exceeded.
It's possible for an administrator to disable login with ALTER LOGIN as
well.
Really what the subtle difference between DENY CONNECT and ALTER LOGIN
DISABLE might be in practice, I can't really see.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||It is not checked. So when the login is locked, i can enable it by clicking
Enable option. What's the need for this checkbox?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23vey2R6fHHA.588@.TK2MSFTNGP06.phx.gbl...
> Is it grey *and* checked or is it grey and *not* checked?
> When SQL Server disables a login, you should be able to uncheck that box.
> You're not allowed to check it, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:e%23sbDL6fHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Thanks Tom,
> Then what's the checkbox in status tab of a login properties (Login is
> locked out). Mine is gray (I use win XP)
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23tEa0s5fHHA.588@.TK2MSFTNGP06.phx.gbl...
>> Denying a login is done by an administrator to prevent the person from
>> logging into SQL Server. Disabling is done by SQL Server itself. This
>> is
>> typically because someone tried to login with the password unsuccessfully
>> several times and the limit was exceeded.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eYmc6p5fHHA.4032@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> 1) What's difference between disabling a login and denying it?
>> 2) What does Denying mean for a login of type SQL Authentication?
>> Thanks in advance,
>> Leila
>>
>|||It's just a convenience to re-enable the login.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23K0Oym6fHHA.4156@.TK2MSFTNGP02.phx.gbl...
It is not checked. So when the login is locked, i can enable it by clicking
Enable option. What's the need for this checkbox?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23vey2R6fHHA.588@.TK2MSFTNGP06.phx.gbl...
> Is it grey *and* checked or is it grey and *not* checked?
> When SQL Server disables a login, you should be able to uncheck that box.
> You're not allowed to check it, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:e%23sbDL6fHHA.4032@.TK2MSFTNGP02.phx.gbl...
> Thanks Tom,
> Then what's the checkbox in status tab of a login properties (Login is
> locked out). Mine is gray (I use win XP)
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23tEa0s5fHHA.588@.TK2MSFTNGP06.phx.gbl...
>> Denying a login is done by an administrator to prevent the person from
>> logging into SQL Server. Disabling is done by SQL Server itself. This
>> is
>> typically because someone tried to login with the password unsuccessfully
>> several times and the limit was exceeded.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eYmc6p5fHHA.4032@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> 1) What's difference between disabling a login and denying it?
>> 2) What does Denying mean for a login of type SQL Authentication?
>> Thanks in advance,
>> Leila
>>
>|||> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
> DISABLE might be in practice, I can't really see.
I agree regarding SQL Server logins.
Perhaps MS just wanted to be consistent, and allow both DISABLE and DENY for both Windows *and* SQL
logins? I presume that for Windows logins there's a big difference. Say Joe is a Windows login and
is also member of a windows group that has a login. DENY on Joe would mean "Joe cannot connect.".
DISABLE would mean "Joe can connect through his windows group membership.".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9914851B9626Yazorman@.127.0.0.1...
> Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
>> Denying a login is done by an administrator to prevent the person from
>> logging into SQL Server. Disabling is done by SQL Server itself. This is
>> typically because someone tried to login with the password unsuccessfully
>> several times and the limit was exceeded.
> It's possible for an administrator to disable login with ALTER LOGIN as
> well.
> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
> DISABLE might be in practice, I can't really see.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Is it really reasonable that when DBA disables a login, it can still be
authenticated via other way? In what situation is it useful?
I cannot understand the interaction between Deny and Disable status for a
login. Which one takes precedence?
Could you please explain more about your example.
Thanks indeed.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OU8oZhAgHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
>> DISABLE might be in practice, I can't really see.
> I agree regarding SQL Server logins.
> Perhaps MS just wanted to be consistent, and allow both DISABLE and DENY
> for both Windows *and* SQL logins? I presume that for Windows logins
> there's a big difference. Say Joe is a Windows login and is also member of
> a windows group that has a login. DENY on Joe would mean "Joe cannot
> connect.". DISABLE would mean "Joe can connect through his windows group
> membership.".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9914851B9626Yazorman@.127.0.0.1...
>> Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
>> Denying a login is done by an administrator to prevent the person from
>> logging into SQL Server. Disabling is done by SQL Server itself. This
>> is
>> typically because someone tried to login with the password
>> unsuccessfully
>> several times and the limit was exceeded.
>> It's possible for an administrator to disable login with ALTER LOGIN as
>> well.
>> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
>> DISABLE might be in practice, I can't really see.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Seems I was incorrect. I just disabled the Windows login I'm currently using, and although I'm
member of the Administrators group (which is also a login), I couldn't login to my SQL Server.
So, just to be certain that I didn't do something fishy, I dropped that disabled login, and now I
could connect (through my windows group membership).
Makes me too wonder why we can both disable and deny a login...
Hmm, hang on. Perhaps this is relevant for logins that aren't traditional logins? Perhaps DISABLE is
important for logins that are created for certificates or symmetric keys? This is just speculation,
but my thinking is that perhaps you cannot DENY CONNECT on such a login, and this is where DISABLE
is needed? I'm afraid that I don't have such a setup where I can test it, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:%23gU8D4EgHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Is it really reasonable that when DBA disables a login, it can still be authenticated via other
> way? In what situation is it useful?
> I cannot understand the interaction between Deny and Disable status for a login. Which one takes
> precedence?
> Could you please explain more about your example.
> Thanks indeed.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OU8oZhAgHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
>> DISABLE might be in practice, I can't really see.
>> I agree regarding SQL Server logins.
>> Perhaps MS just wanted to be consistent, and allow both DISABLE and DENY for both Windows *and*
>> SQL logins? I presume that for Windows logins there's a big difference. Say Joe is a Windows
>> login and is also member of a windows group that has a login. DENY on Joe would mean "Joe cannot
>> connect.". DISABLE would mean "Joe can connect through his windows group membership.".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns9914851B9626Yazorman@.127.0.0.1...
>> Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
>> Denying a login is done by an administrator to prevent the person from
>> logging into SQL Server. Disabling is done by SQL Server itself. This is
>> typically because someone tried to login with the password unsuccessfully
>> several times and the limit was exceeded.
>> It's possible for an administrator to disable login with ALTER LOGIN as
>> well.
>> Really what the subtle difference between DENY CONNECT and ALTER LOGIN
>> DISABLE might be in practice, I can't really see.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Hmm, hang on. Perhaps this is relevant for logins that aren't
> traditional logins? Perhaps DISABLE is important for logins that are
> created for certificates or symmetric keys? This is just speculation,
> but my thinking is that perhaps you cannot DENY CONNECT on such a login,
> and this is where DISABLE is needed? I'm afraid that I don't have such a
> setup where I can test it, though...
But you cannot connect with these logins anyway, so DENY CONNECT is
pointless. And DISABLE? Currently this login cannot be granted any rights?
I like your original idea. Say that user DOMAIN\User has access through
DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
way lock out this user, but let the rest of DOMAIN\Group in?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> But you cannot connect with these logins anyway, so DENY CONNECT is
> pointless.
Yes, that was exactly my point.
> And DISABLE? Currently this login cannot be granted any rights?
But I see DISABLE as a way of inactivating it (like removing it temporarily)...?
> I like your original idea. Say that user DOMAIN\User has access through
> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
> way lock out this user, but let the rest of DOMAIN\Group in?
I guess so, DENY should take precedence. My original thought was that DENY takes precedence but
DISABLE would "removing it without removing it". But my test showed that DISABLE had that very same
effect, so I'm still confused to why we have both DISABLE and DENY. Perhaps it is just two different
object types (login as a general concept, and connecting to the server), and in the end the two
doing the same thing (if that is indeed correct) isn't something we need to worry about?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99151075BB6DAYazorman@.127.0.0.1...
> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a login,
>> and this is where DISABLE is needed? I'm afraid that I don't have such a
>> setup where I can test it, though...
> But you cannot connect with these logins anyway, so DENY CONNECT is
> pointless. And DISABLE? Currently this login cannot be granted any rights?
> I like your original idea. Say that user DOMAIN\User has access through
> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
> way lock out this user, but let the rest of DOMAIN\Group in?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||When a student asks about the difference between these two in SQL class and
you can only give him an answer including "perhaps" or "I guess" or etc...
you'll then worry like me! ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
> Yes, that was exactly my point.
>
>> And DISABLE? Currently this login cannot be granted any rights?
> But I see DISABLE as a way of inactivating it (like removing it
> temporarily)...?
>
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
> I guess so, DENY should take precedence. My original thought was that DENY
> takes precedence but DISABLE would "removing it without removing it". But
> my test showed that DISABLE had that very same effect, so I'm still
> confused to why we have both DISABLE and DENY. Perhaps it is just two
> different object types (login as a general concept, and connecting to the
> server), and in the end the two doing the same thing (if that is indeed
> correct) isn't something we need to worry about?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a login,
>> and this is where DISABLE is needed? I'm afraid that I don't have such a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||I have to make my best guesses a lot, when I am teaching. Especially when
students ask me the WHY questions. I will sometimes attempt to find out a
better answer, if I think the question warrants it, but other times I just
figure that it's just the way it is. I don't worry at all that there are
many things I don't know, as long as there are lots of things I do know, and
I keep trying to learn more and help others learn more.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
> When a student asks about the difference between these two in SQL class
> and you can only give him an answer including "perhaps" or "I guess" or
> etc... you'll then worry like me! ;-)
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is just
>> two different object types (login as a general concept, and connecting to
>> the server), and in the end the two doing the same thing (if that is
>> indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>> login,
>> and this is where DISABLE is needed? I'm afraid that I don't have such
>> a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Denying Connect and Disabling a login are conceptually two different things
enforced by different code in different parts of the code. The fact that
they happen to exhibit the same behavior in the one thing you're testing
shouldn't cause too much confusion. Connect privileges are an Endpoint
thing so for example a login may be enabled and have connect privileges in
TSQL but not in Service Broker or Database Mirroring. You will also see
different behavior between the two when you try to impersonate a login.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OxhQXYPgHHA.3368@.TK2MSFTNGP03.phx.gbl...
>I have to make my best guesses a lot, when I am teaching. Especially when
>students ask me the WHY questions. I will sometimes attempt to find out a
>better answer, if I think the question warrants it, but other times I just
>figure that it's just the way it is. I don't worry at all that there are
>many things I don't know, as long as there are lots of things I do know,
>and I keep trying to learn more and help others learn more.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> When a student asks about the difference between these two in SQL class
>> and you can only give him an answer including "perhaps" or "I guess" or
>> etc... you'll then worry like me! ;-)
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is just
>> two different object types (login as a general concept, and connecting
>> to the server), and in the end the two doing the same thing (if that is
>> indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com)
>> writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>> login,
>> and this is where DISABLE is needed? I'm afraid that I don't have such
>> a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Thanks Kalen :-)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OxhQXYPgHHA.3368@.TK2MSFTNGP03.phx.gbl...
>I have to make my best guesses a lot, when I am teaching. Especially when
>students ask me the WHY questions. I will sometimes attempt to find out a
>better answer, if I think the question warrants it, but other times I just
>figure that it's just the way it is. I don't worry at all that there are
>many things I don't know, as long as there are lots of things I do know,
>and I keep trying to learn more and help others learn more.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> When a student asks about the difference between these two in SQL class
>> and you can only give him an answer including "perhaps" or "I guess" or
>> etc... you'll then worry like me! ;-)
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is just
>> two different object types (login as a general concept, and connecting
>> to the server), and in the end the two doing the same thing (if that is
>> indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com)
>> writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>> login,
>> and this is where DISABLE is needed? I'm afraid that I don't have such
>> a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>|||Thanks Roger!
What I understood is that connect permission has wider definition and is
able to cover even definition of Enable/Disable feature.
It is still unclear for me that what Enable/Disable of SQL login can do that
Grant/Deny Connect cannot.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:5B0EAA4D-5F1E-4F69-BB11-3A573ECDEAC0@.microsoft.com...
> Denying Connect and Disabling a login are conceptually two different
> things enforced by different code in different parts of the code. The
> fact that they happen to exhibit the same behavior in the one thing you're
> testing shouldn't cause too much confusion. Connect privileges are an
> Endpoint thing so for example a login may be enabled and have connect
> privileges in TSQL but not in Service Broker or Database Mirroring. You
> will also see different behavior between the two when you try to
> impersonate a login.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OxhQXYPgHHA.3368@.TK2MSFTNGP03.phx.gbl...
>>I have to make my best guesses a lot, when I am teaching. Especially when
>>students ask me the WHY questions. I will sometimes attempt to find out a
>>better answer, if I think the question warrants it, but other times I just
>>figure that it's just the way it is. I don't worry at all that there are
>>many things I don't know, as long as there are lots of things I do know,
>>and I keep trying to learn more and help others learn more.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> When a student asks about the difference between these two in SQL class
>> and you can only give him an answer including "perhaps" or "I guess" or
>> etc... you'll then worry like me! ;-)
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is just
>> two different object types (login as a general concept, and connecting
>> to the server), and in the end the two doing the same thing (if that is
>> indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com)
>> writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>> login,
>> and this is where DISABLE is needed? I'm afraid that I don't have
>> such a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>>
>|||With disable login you can prevent anyone from logging in on any endpoint
using that login. With deny connect you can allow logins through some
endpoints and deny them through others. For example, you can allow someone
to login through TDS but not through HTTP or vice versa.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Leila" <Leilas@.hotpop.com> wrote in message
news:O2ecaRZgHHA.4952@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen :-)
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OxhQXYPgHHA.3368@.TK2MSFTNGP03.phx.gbl...
>>I have to make my best guesses a lot, when I am teaching. Especially when
>>students ask me the WHY questions. I will sometimes attempt to find out a
>>better answer, if I think the question warrants it, but other times I just
>>figure that it's just the way it is. I don't worry at all that there are
>>many things I don't know, as long as there are lots of things I do know,
>>and I keep trying to learn more and help others learn more.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> When a student asks about the difference between these two in SQL class
>> and you can only give him an answer including "perhaps" or "I guess" or
>> etc... you'll then worry like me! ;-)
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is just
>> two different object types (login as a general concept, and connecting
>> to the server), and in the end the two doing the same thing (if that is
>> indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com)
>> writes:
>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>> traditional logins? Perhaps DISABLE is important for logins that are
>> created for certificates or symmetric keys? This is just speculation,
>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>> login,
>> and this is where DISABLE is needed? I'm afraid that I don't have
>> such a
>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>>
>|||That was really helpful, thanks indeed Roger! :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:AFDAA372-0933-45DA-998E-345F3293083D@.microsoft.com...
> With disable login you can prevent anyone from logging in on any endpoint
> using that login. With deny connect you can allow logins through some
> endpoints and deny them through others. For example, you can allow
> someone to login through TDS but not through HTTP or vice versa.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O2ecaRZgHHA.4952@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kalen :-)
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OxhQXYPgHHA.3368@.TK2MSFTNGP03.phx.gbl...
>>I have to make my best guesses a lot, when I am teaching. Especially when
>>students ask me the WHY questions. I will sometimes attempt to find out a
>>better answer, if I think the question warrants it, but other times I
>>just figure that it's just the way it is. I don't worry at all that there
>>are many things I don't know, as long as there are lots of things I do
>>know, and I keep trying to learn more and help others learn more.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eCnaW$LgHHA.284@.TK2MSFTNGP05.phx.gbl...
>> When a student asks about the difference between these two in SQL class
>> and you can only give him an answer including "perhaps" or "I guess" or
>> etc... you'll then worry like me! ;-)
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:ujJfeaIgHHA.4064@.TK2MSFTNGP03.phx.gbl...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless.
>> Yes, that was exactly my point.
>>
>> And DISABLE? Currently this login cannot be granted any rights?
>> But I see DISABLE as a way of inactivating it (like removing it
>> temporarily)...?
>>
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>> I guess so, DENY should take precedence. My original thought was that
>> DENY takes precedence but DISABLE would "removing it without removing
>> it". But my test showed that DISABLE had that very same effect, so I'm
>> still confused to why we have both DISABLE and DENY. Perhaps it is
>> just two different object types (login as a general concept, and
>> connecting to the server), and in the end the two doing the same thing
>> (if that is indeed correct) isn't something we need to worry about?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns99151075BB6DAYazorman@.127.0.0.1...
>> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com)
>> writes:
>>> Hmm, hang on. Perhaps this is relevant for logins that aren't
>>> traditional logins? Perhaps DISABLE is important for logins that are
>>> created for certificates or symmetric keys? This is just
>>> speculation,
>>> but my thinking is that perhaps you cannot DENY CONNECT on such a
>>> login,
>>> and this is where DISABLE is needed? I'm afraid that I don't have
>>> such a
>>> setup where I can test it, though...
>> But you cannot connect with these logins anyway, so DENY CONNECT is
>> pointless. And DISABLE? Currently this login cannot be granted any
>> rights?
>> I like your original idea. Say that user DOMAIN\User has access
>> through
>> DOMAIN\Group. If we now to DENY CONNECT to [DOMAIN\User] can we this
>> way lock out this user, but let the rest of DOMAIN\Group in?
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>>
>>
>
没有评论:
发表评论