mibuso.com

Microsoft Business Solutions online community
It is currently Fri Dec 19, 2014 1:55 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Login Stored Procedure -> SQL (sp_$ndo$loginproc)
PostPosted: Thu Jan 29, 2009 8:32 am 
Offline

Joined: Mon Mar 08, 2004 1:42 pm
Posts: 3255
Location: Hannover
Country: Germany (de)
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.

Code: Select all
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.

Code: Select all
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

_________________
Do you make it right, it works too!


Top
 Profile  
 
 Post subject: Re: Login Stored Procedure -> SQL (sp_$ndo$loginproc)
PostPosted: Mon Feb 02, 2009 3:02 pm 
Offline

Joined: Wed May 18, 2005 9:17 am
Posts: 26
Location: Geneva
Country: Switzerland (ch)
Hi,

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

Thanks in advance,

Denis


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 6 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: