How do i Set Password Expiration days for a SQL Login

Question

How do i Set Password Expiration days for a SQL Login

haichells

SSCrazy

Points: 2713
More actions
December 21, 2007 at 1:49 am

#127368

Hi,
I am creating a Login in code while Installation. I have a requirement now that Login should have Password expiration policy. How do i set this? Please help.
Thanks
Chelladurai

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

It is taken from the local security policy, which by default will be taken from the settings on the DC. You dont actually set these values within SQL.

haichells SSCrazy Points: 2713 More actions · Answer 2

Hi,

Thanks for your response. Can you please tell me what is this DC....

Animal Magic SSChampion Points: 13964 More actions · Answer 3

The domain controller (assuming you are running on a windows domain) - ask your network admin what the settings are.

haichells SSCrazy Points: 2713 More actions · Answer 4

Hi,

I need to apply the Expiration policy for SQL Logins. Network domain anyway will maintain windows expiration policy i understand.

My query is can we define expiration days/intervals for Sql Logins.

Animal Magic SSChampion Points: 13964 More actions · Answer 5

haichells (12/21/2007)
Hi,
I need to apply the Expiration policy for SQL Logins. Network domain anyway will maintain windows expiration policy i understand.
My query is can we define expiration days/intervals for Sql Logins.

Yes you can, but it still takes the settings from the security policy. You cant set anything in sql (as far as i know) that will change the expiration periods of sql logins.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 6

Animal Magic (12/21/2007)
It is taken from the local security policy, which by default will be taken from the settings on the DC. You dont actually set these values within SQL.

You're correct in saying it's defined in the local security policy. But you are wrong in saying that this policy is taken from the Domain Controller. SQL login are treated like local accounts and only the local policy applies to them. Domain policies can overrule local policies for Domain Users, but not for local accounts or SQL accounts.

[font="Verdana"]Markus Bohse[/font]

Animal Magic SSChampion Points: 13964 More actions · Answer 7

Animal Magic

SSChampion

Points: 13964

December 21, 2007 at 2:41 am

#762019

ps, search for password policy in BOL.

Animal Magic SSChampion Points: 13964 More actions · Answer 8

MarkusB (12/21/2007)
Animal Magic (12/21/2007)
It is taken from the local security policy, which by default will be taken from the settings on the DC. You dont actually set these values within SQL.
You're correct in saying it's defined in the local security policy. But you are wrong in saying that this policy is taken from the Domain Controller. SQL login are treated like local accounts and only the local policy applies to them. Domain policies can overrule local policies for Domain Users, but not for local accounts or SQL accounts.

Thanks for clearing that up Markus, i didnt realise at first that we were talking about sql logins only.

haichells SSCrazy Points: 2713 More actions · Answer 9

Hi,

Thanks for all your response. Can you please let me know how to check the local security policy on sql server.

mike.stone.email SSC Enthusiast Points: 170 More actions · Answer 10

Hi! Does anybody know how I can get a notification that someone's password will expire in another n days? or to trigger an SP to take action n days before expiry? I'm assuming an SQL server login with Enforce Password Policy and Enforce Password Expiration. Thanks in advance ...

Oliiii SSCertifiable Points: 5328 More actions · Answer 11

No need to cross post 🙂

http://www.sqlservercentral.com/Forums/Topic435492-149-1.aspx

SkyBVI SSCoach Points: 15128 More actions · Answer 12

@ mike

/* users, locked or not, and days until expiration */

use master;

go

set nocount on;

go

declare @loginname varchar(200);

declare @logintbl table (

LoginName varchar(20) ,

IsLocked char(5) ,

DaysUntilExpiration int);

declare c_logins cursor

for

select [name] from sys.syslogins

where name in (

select USERID from DYNAMICS..SY01400);

open c_logins;

fetch next from c_logins into @loginname;

while @@FETCH_STATUS = 0

begin

insert @logintbl(LoginName, IsLocked, DaysUntilExpiration)

select

@loginname

,case convert(smallint, LOGINPROPERTY(@loginname, 'IsLocked')) when 0 then 'No' when 1 then 'Yes' end

,convert(int, LOGINPROPERTY(@loginname, 'DaysUntilExpiration'));

fetch next from c_logins into @loginname;

end

close c_logins;

deallocate c_logins;

select * from @logintbl;

go

set nocount off;

go

Regards
Sushant Kumar
MCTS,MCP

mike.stone.email SSC Enthusiast Points: 170 More actions · Answer 13

Thanks SKYBVI for your detailed answer! It's exactly what I needed. 🙂

mike.stone.email SSC Enthusiast Points: 170 More actions · Answer 14

Oops! I stand corrected :blush: and thanks, Oliiii, for the link toLOGINPROPERTY you provided me in the other thread.