Login Stored Procedure -> SQL (sp_$ndo$loginproc)

garak

A login stored procedure is a stored procedure that you can use to perform predefined functions after a user logs on to Microsoft Dynamics NAV with Microsoft SQL Server. For exampe to send the user a message, that the database is currently in single user mode for maintenance.

The very important hing here is, that these "login stored procedure" is run immediately after the user has logged on to SQL Server and database, but before Microsoft Dynamics NAV carries out any tasks.

The stored procedure is created in the database and has a predefined name and a list of parameters.
The name of the stored procedure must be: sp_$ndo$loginproc

It has the following characteristics:

* It takes two VARCHAR parameters: the name of the application and the C/SIDE version number. These parameters must be declared as part of the stored procedure but do not have to be used.

* It can perform transactions. Microsoft Dynamics NAV uses a COMMIT to flush any outstanding transactions after the stored procedure has finished executing.

* The RAISERROR statement can be used to display an error message in Microsoft Dynamics NAV and prevent the user from accessing the database.

* The PRINT statement can be used to display a warning in Microsoft Dynamics NAV and allow the user to access the database.

* If the stored procedure returns a value, it is ignored.

* If the stored procedure does not exist, no action is taken by Microsoft Dynamics NAV and the login process continues as usual.


To understand this better, here two simple examples.
The first example is only a infomessage that allowes the login, the second is an error that prevents the login.

Example 1 displays a warning message in Microsoft Dynamics NAV and permits the login.

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
PRINT 'The system will be unavailable on Sunday April 1.'
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

Example 2 displays an error message in Microsoft Dynamics NAV and prevents the login.

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 ('ACCOUNTS\jim', 'SALES\bill')
RAISERROR ('Contact the system administrator.', 11, 1)
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

So good luck and enjoy this Tip&Trick.

Regards

Denis_Vitse

Hi,

that's good to know!
Do you know if other pre defined Sp's can be used with NAV?

Thanks in advance,

Denis

arcrex

Hello, can you please help me with one problem I have using this feature?

I am trying to compare actual pwd hash with stored old one of logged user before opening company ...
When logging as NAV superuser, everything is OK and I get actual hash.
But when logging as normal NAV user, I get empty string (I convert varbinary to varchar to make it simple)

So I suppose there is some rights problem but donno how to solve it.

Problematic is selecting hashes from [sys].[sql_logins] from master database.

Can you help me with that ?

Thank you

kriki

Probably, it is a problem that a normal user hasn't access to that table/db This is NOT a NAV security problem but a SQL one.