Policy for certain logins password expiration check

Question

Post reply

Policy for certain logins password expiration check

dallas13

SSCertifiable

Points: 7491
More actions
February 17, 2010 at 12:20 pm

#386328

I have to create a policy on my server which checks password expiration enabled for each login or not.
I have certain users, whose passwords never should expire and all of them ends with word ‘%user’. Like testuser.
I want the policy to check login name first
if its like testuser then check-password expiration =Disabled
If any other login-name then check-password expiration =Enabled
What condition should I write? I am not sure how to use ExecuteSQL() condition to achieve this. Appreciate any help on this matter.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I don't think this is what you need. I searched quite a bit:

http://blogs.msdn.com/sqlpbm/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 2

A bit belated, but this should do it:

First use the Login Options facet to create a condition PwdExpiration with @PasswordExpirationEnabled = False. Then use the Login Options facet to create a condition TestUserPwdExpiration with @name LIKE '%testuser%' Create a policy with the Check Condition PwdExpiration and drop the 'Against targets' field down to select TestUserPwdExpiration.

Cheers,

Cath

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

Thanks for your replies.

So accoring to your answer I need to create 2 conditions

1) PwdExpiration ---@PasswordExpirationEnabled = False use login options facet

2) TestUserPwdExpiration---@name LIKE '%testuser%' use login facet

Now I need to create a policy and associate these 2 conditions to the policy. I have created a policy and first check condition was PwdExpiration

and under that I have used other condition TestUserPwdExpiration so it checks only logins whose names like '%testuser'.

This is perfectly fine.

--------------------------------------------------------

But I also need to see passwordexpired for other logins like sa or domain\user or other logins not like '%testuser'. and I have to create another policy for this purpose.

but I dont want to create another policy. Can I use Executesql() task or any other way so that I can chek,

If username like '%testuser' then pwdexpired =false

if any other username then pwdexpired = true IN ONE POLICY.

Thanks

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Why not add a CASE Statement or some other logic to your query. Maybe I'm missing something. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/