Login Stored Procedure

A login stored procedure is a stored procedure that you can use to perform predefined functions after a user logs on to the Microsoft Dynamics NAV Development Environment. A typical function would be to generate a message that informs the user that the database is currently in single-user mode so that an administrator can perform database maintenance tasks and is therefore inaccessible.

The login stored procedure is run immediately after the user has logged on to SQL Server and opened a database, and before Dynamics NAV performs any tasks including executing C/AL triggers.

Note

In Microsoft Dynamics NAV 2018, the Microsoft Dynamics NAV Server instance does not call login stored procedures. The development environment does call login stored procedures.

Creating the Stored Procedure

The stored procedure is created in the database and has a predefined name and a list of parameters.

The stored procedure is called [sp_$ndo$loginproc] and has the following characteristics:

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

  • It can perform transactions. 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 Dynamics NAV and prevent the user from accessing the database.

  • The PRINT statement can be used to display a warning in Dynamics NAV and let the user 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 Dynamics NAV and the login process continues.

Example 1

The following code example displays a warning message in 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 April 1, 2013.'  
END  
GO  
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public  
GO  

Example 2

The following code example displays an error message in 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  

See Also

Table Keys and Performance
Bulk Inserts
C/AL Database Functions and Performance on SQL Server