Password Expiration Notice Handling

onecm

Dear All,

Currently, I want to alert user for their passowrd expiration during Navision logon.

I searched for solution and found that I can use stored procedure 'sp_$ndo$loginproc' to do that.

I finally write the following code :

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_$ndo$loginproc' AND type = 'P')
DROP PROCEDURE [sp_$ndo$loginproc]
GO
CREATE PROCEDURE [sp_$ndo$loginproc]
@appname VARCHAR(64) = NULL,
@appversion VARCHAR(16) = NULL
AS
BEGIN

IF SUSER_SNAME() IN (
SELECT SL.name AS LoginName 
FROM sys.sql_logins AS SL 
WHERE (is_expiration_checked = 1 and LOGINPROPERTY (SL.name, 'DaysUntilExpiration') = 2)  )
PRINT 'Your password will be expired within 2 days, please change it ! '

IF SUSER_SNAME() IN (
SELECT SL.name AS LoginName 
FROM sys.sql_logins AS SL 
WHERE (is_expiration_checked = 1 and LOGINPROPERTY (SL.name, 'DaysUntilExpiration') = 1)  )
PRINT 'Your password will be expired within 1 days, please change it ! '

END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

I tested it and found that it only work for user with sysadmin role in SQL server.

As I only want those users with public role to have the alert message. Can anyone help me to solve it ?

Many thanks for your kindly help !

Dakkon

You could create a new table in your Navision database that will store an OnLogin notice. You could then insert a notification from a stored proc that gets run once a day by an automated task. Then when a user logs in you could check the table for notifications meant for that user, display them and then delete them.