sp_$ndo$loginproc Login Stored Procedure on the SQL Server Option

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. A typical function would be to generate a message informing 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 Microsoft Dynamics NAV carries out any tasks including executing any C/AL triggers. The user must have successfully logged on to the server and have access to the database before the stored procedure is run.

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 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.

The following examples show how to create a login procedure in the Query Analyzer tool. The database must be selected before these statements are executed.

Example 1

The following code example 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

The following code example 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