SQL Server Authentication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server Authentication

Author

Topic

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-18 : 13:54:50

SQL Server 2005 SP2

Is there a way to find out when SQL Server managed logins will expire.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 14:38:56

Try below:-

SELECT name,
LOGINPROPERTY(name, 'DaysUntilExpiration') 
FROM sys.sql_logins

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-18 : 15:15:07

Did not work for me.

Tried on a few different machines and seems to only return null

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 15:19:46

Check this:
http://msdn.microsoft.com/en-us/library/ms345412.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 15:26:58

quote:
Originally posted by acollins74

Did not work for me.

Tried on a few different machines and seems to only return null

Probably because they are set to not expire.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-18 : 15:33:59

Enforce password expiration is set to on within SSMS.
Does Local group policies need to be check as well?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 15:38:45

The enforce password policy must be enabled.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-18 : 15:50:25

Agreed.

Can I assume this returns other than null for you?
SELECT name,
LOGINPROPERTY(name, 'DaysUntilExpiration')
FROM sys.sql_logins

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 15:53:10

I don't use that option as our SQL accounts are used by the application rather than a specific user.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-18 : 15:57:04

Wouldn't it be exciting to give it a try though.
I have tried on both 2008 and 2005 but don't seem to get expected results for 'DaysUntilExpiration'
I am successfull using other parameters listed such as 'IsLocked'

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-09-19 : 04:16:12

Hi I try it in a test server 2005 and it returned me all the logins and the date as null probably because The enforce password expiration option wasn't enabled. I tried it with enable the password expiratio and I got the same results. The mode is mixed and at the test that I run it I had both SQL and Win authentication users. What kind of authentication do you use?

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-19 : 08:08:24

lepeniotis, we use Mixed mode for most everything.

So if I understand; this did not work in your environment either.

I do notice that
SELECT name, LOGINPROPERTY(name, 'DaysUntilExpiration')
FROM sys.sql_logins

yeilds the same as
SELECT name, LOGINPROPERTY(name, 'anytexthere')
FROM sys.sql_logins

So though it seems silly I have to wonder if 'DaysUntilExpiration' is the correct string to pass and BOL is not providing the accurate string.

Anyone able to get this to work?

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-09-19 : 08:25:28

Look what you can try!

at anytexthere put 'PasswordLastSetTime' according to the policy of password expiration you can create a report that acts like this

for example

(passwordLastSetTime + passwordPolicyPeriod ) - getDate

I think that you will give you the results that you want.

However msdn says that DaysUntilExpiration returns directly the value that you want.

But I think that the logins return null because they don't have expiration date.

Regards

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-09-19 : 10:09:05

Here are the results of some testing.
SELECT name, LOGINPROPERTY(name, 'DaysUntilExpiration')
FROM sys.sql_logins

SQL Server 2005 EE SP2 CU9 - Does not work (even though BOL sez compatible w/2005)
SQL Server 2008 Developers Edition RTM - Does not work (I thought DE should be the same as EE minus licensing?)
SQL Server 2008 EE RTM - Works great.

This was a painful process but at least it ends with some clarity. Though, this would be very helpful for me if it worked in 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources