logins lock

hi ,
how can we lock and login on a sql server as we can do it
in sybase by sp_locklogin?
do we an alternate solution as we dont have sp_locklogin
sp in mssql 2k .Hi,
Only Windows based users can be locked. SQL server by itself do not have any
user or password polycies.
So for SQL server based logins we can not set the lockout option.
Thanks
Hari
MCDBA
"sid" <sid_m15@.coolgoose.com> wrote in message
news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
> hi ,
> how can we lock and login on a sql server as we can do it
> in sybase by sp_locklogin?
> do we an alternate solution as we dont have sp_locklogin
> sp in mssql 2k .
>|||This will be possible for SQL logins in SQL2005 however.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Only Windows based users can be locked. SQL server by itself do not have
> any
> user or password polycies.
> So for SQL server based logins we can not set the lockout option.
>
>
> Thanks
> Hari
> MCDBA
> "sid" <sid_m15@.coolgoose.com> wrote in message
> news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
>|||thanks a lot
sid
>--Original Message--
>This will be possible for SQL logins in SQL2005 however.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>http://www.sqldbatips.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in
message
>news:OtKeCpTgEHA.3548@.TK2MSFTNGP09.phx.gbl...
itself do not have[vbcol=seagreen]
lockout option.[vbcol=seagreen]
it[vbcol=seagreen]
sp_locklogin[vbcol=seagreen]
>
>.
>|||thanks a lot
sid
>--Original Message--
>Hi,
>Only Windows based users can be locked. SQL server by
itself do not have any
>user or password polycies.
>So for SQL server based logins we can not set the lockout
option.
>
>
>Thanks
>Hari
>MCDBA
>"sid" <sid_m15@.coolgoose.com> wrote in message
>news:571801c4811d$8d8e6290$a501280a@.phx.gbl...
it[vbcol=seagreen]
>
>.
>

Login/Account Lock

I have a scenario in my application that I need to know whether the account
is locked or not before the password is reset. If the account is locked the
app should pop up a msg.
how to programmatically determine that the account is locked or not in sql
server 2005.
Some thing like @.@.islocked or @.@.lock_status or any system SP
Can any one help me as this is an urgent case needed immediate response.
Thanks in advance.
Best regards,
venkateshVenkatesh (Venkatesh@.discussions.microsoft.com) writes:
> I have a scenario in my application that I need to know whether the
> account is locked or not before the password is reset. If the account is
> locked the app should pop up a msg.
> how to programmatically determine that the account is locked or not in sql
> server 2005.
> Some thing like @.@.islocked or @.@.lock_status or any system SP
> Can any one help me as this is an urgent case needed immediate response.
sys.server_principals.is_disabled seems like it could be what you are
asking for.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for the immediate response. But I think this will disable the login -
to disable the login we can very well use the below command
alter login [loginname] disable/enable
But what I want is to determine whether account is locked or not. When you
click the properties of a login in the status tab you would see the account
lock out check box. I want how programatically this can be determined
Correct me If I am wrong. In case if your code
sys.server_principals.is_disabled is used to lock the account could you give
me an example.
"Erland Sommarskog" wrote:

> Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> sys.server_principals.is_disabled seems like it could be what you are
> asking for.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||You can use select LOGINPROPERTY('test', N'IsLocked').
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> Thanks for the immediate response. But I think this will disable the
> login - to disable the login we can very well use the below command
> alter login [loginname] disable/enable
> But what I want is to determine whether account is locked or not. When
> you click the properties of a login in the status tab you would see the
> account lock out check box. I want how programatically this can be
> determined
> Correct me If I am wrong. In case if your code
> sys.server_principals.is_disabled is used to lock the account could you
> give me an example.
OK, I admit that I took a gamble on that one.
It seems that Mohit did a better job on reading the manual that you and I
did.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Login Properties: Deny, Disable, Lock Out? Which do I use?

Hey again!

I have a SQL login, and I want to prevent the login from logging into SQL Server.

From SSMS, I connect to the Database Engine, expand Security, Logins.
I right-click the login in question and select Properties. I select the Status page.

So I see I can deny, disable and lock out this login.
What is the difference between these three options?

Also, shouldn't the checkbox "Login is locked out" be checkable?
Currently, it's unchecked/disabled and I cannot alter this value.

I performed a cross-reference of the possible combinations:

SQL Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
UserName (valid) Grant Enabled No 0
UserName (valid) Grant Enabled Yes 18486
UserName (valid) Grant Disabled No 18470
UserName (valid) Grant Disabled Yes 18470
UserName (valid) Deny Enabled No 18456
UserName (valid) Deny Enabled Yes 18486
UserName (valid) Deny Disabled No 18470
UserName (valid) Deny Disabled Yes 18470
UserName (garbage) Grant Enabled No 18456
UserName (garbage) Grant Enabled Yes 18456
UserName (garbage) Grant Disabled No 18456
UserName (garbage) Grant Disabled Yes 18456
UserName (garbage) Deny Enabled No 18456
UserName (garbage) Deny Enabled Yes 18456
UserName (garbage) Deny Disabled No 18456
UserName (garbage) Deny Disabled Yes 18456
(garbage) (garbage) Either Either No 18456

NT-Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
DOMAIN\UserName N/A Grant Enabled N/A 0
DOMAIN\UserName N/A Deny Enabled N/A 18456
DOMAIN\UserName N/A Grant Disabled N/A 18470
DOMAIN\UserName N/A Deny Disabled N/A 18470

where @.@.ERROR
0 = Connects
18456 = Login failed for user 'UserName'.
18470 = Login failed for user 'UserName'. Reason: The account is disabled.
18486 = Login failed for user 'UserName' because the account is currently locked out. The system administrator can unlock it.

(Upon third garbage-password, the SQL-account is locked.)

|||

A plain 18456 "login failed" message could mean
- the user is using an incorrect username
- the user is using an incorrect password
- the user isn't defined to the datasource
- the user is denied access.

Based on these findings, I like the "disabled" setting over "denied." If a user calls me with an 18470 message, I have some confidence in that the user connecting with a valid username and password.