NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

Well, it is a frequently asked question „How to prevent multiple logins of the same user in NAV?". Usually the reason for this is to save client-licenses, or other things.

When browsing the known web-forums you'll find several approaches to perform this; from executing 3rd-party applications to kill processes, to sending Alt-F4 keys via "Windows Scripting Host" to manually kill a session in "Session Monitor" (NAV) or "Activity Monitor" (SQL Server).

I want to show another - IMHO very convenient - way to do this, using the not-so-well-known SQL stored procedure "sp_$ndo$loginproc". This is a procedure for NAV (since 4.0 if I don't err) which does not exist out-of-the-box, but could be easily created (as described in some NAV documentation, I don't recall which PDF it was ... sorry Confused):

use [Navision]
go

create procedure [sp_$ndo$loginproc]
@appname varchar(64) = NULL,
@appversion varchar(16) = NULL
as
begin
-- do whatever you want to do
end
go

grant execute on [sp_$ndo$loginproc] to [public]
go

This procedure will be executed from the C/SIDE client when logging on to the SQL Server; thus, it's some kind of auto-start procedure. Hence, all we need to do is to scribble some TSQL in it to 1) check the login and 2) deny access if applicable:

use [Navision]
go

create procedure [sp_$ndo$loginproc]
@appname varchar(64) = NULL,
@appversion varchar(16) = NULL
as
begin

declare @login_count int

if exists(
  select ses.[login_name] from sys.dm_exec_sessions (nolock) ses
  left join master..sysprocesses (nolock) pro on pro.[spid] =  ses.[session_id]
  where ses.[program_name] in ('Microsoft Business Solutions-Navision client', 'Microsoft Dynamics NAV client')
  and pro.[dbid] = db_id()
  and upper(ses.[login_name]) = upper(suser_name())
  group by ses.[login_name]
  having count(*) > 1  -- change threshold if neccessary
) begin
    select @login_count = count(*) - 1 from sys.dm_exec_sessions (nolock) ses
    left join master..sysprocesses (nolock) pro on pro.[spid] =  ses.[session_id]
    where ses.[program_name] in ('Microsoft Business Solutions-Navision client', 'Microsoft Dynamics NAV client')
    and pro.[dbid] = db_id()
    and upper(ses.[login_name]) = upper(suser_name())
    group by ses.[login_name]
    having count(*) > 1  -- change threshold if neccessary

    raiserror ('You are already logged on %i time(s) to the system. Access is denied. Contact your System-Administrator.', 18, 1, @login_count)
    return
  end
end
go

grant execute on [sp_$ndo$loginproc] to [public]
go

This script applies to SQL Server 2005, but it should be easily possible to transform this into SQL 2000 if necessary. Just something to point out:

  • The "Program Name" is hard-coded and might be changed if the NAV application name changes.
  • In my example the number of allowed logins is 1; the error would be raised with the second login attempt. You could simply change the threshold; and you could also read the threshold per user from some setup if available; for example

declare @max_logins int
set @max_logins = 1
select @max_logins = [Max_ Logins] from [dbo].[CRONUS$User Setup] where [User ID] = upper(suser_name)

(Here "Max. Logins" is supposed to be a new field in table "User Setup")

  • then the compare should be

having count(*) > @max_logins

So what happens then: When a user logs on to the system the procedure is executed. If the number of allowed logins per user is exceeded, the procedure would raise an error - displayed in NAV, of course - and cancel the logon process, thus the client stays disconnected, not wasting a precious NAV license.

As always, I appreciate your comments and improvements of the code!

Edit 16.03.2009: Sorry, I forgot to mention this: to be able to read from "sysprocesses" and "sys.dm_exec_sessions " a user has to have VIEW SERVER STATE permissions; the role "PUBLIC" does not include this right out of the box, so is must be added to make the "Login Control" work:

USE [master]

GO

GRANT VIEW SERVER STATE TO PUBLIC

GO

 

 

  • How should I know what possibly could be wrong here?

    Please, just discuss this issue in any NAV forum.

  • Hi,

    How r u?

    I have the same requirement which you have posted.

    I am trying to use the same SP.But I am getting below error.

    Msg 208, Level 16, State 6, Procedure sp_$ndo$logininfo, Line 32

    Invalid object name 'dbo.sp_$ndo$logininfo'.

    Please help asap.

    Regards,

    Preeti

    preetikhanna1404@gmail.com

  • I suggest to discuss this further in some internet forum like MIBUSO.com and/or DynsmicsUsers.net ....

  • Dear Stryk,

    Thank you. I have tried to add some code in function LogInStart (Codeunit LogInManagement) and it works, but the error message appears two times.. @.@ Do you know why the error message appears two times? :D

    Dear Kriki,

    Your session killer is great. Thank you :D

  • Kriki, that's a cool thing!

  • I haven't had time to test it on NAV2013, but I think that with some changes, my session killer (www.mibuso.com/howtoinfo.asp) can work.

  • Hi Johanna,

    well, this trick with the Stored Procedure works directly on SQL Server - but only if a user login actually happens. With NAV 2013 it's ONLY the NST service which really logs on to the SQL Server - no more user delegation -, hence, SQL Server cannot handle the individual logons as those now happen only on the NST. So I guess with NAV 2013 you need to develop some code within NAV to handle the sessions ... !?

  • Dear Stryk,

    Thank you so much for this article.

    Do you know how to Prevent multiple Logins in NAV 2013 RTC?

    Please help. Thank you. :)

    Best regards,

    Johanna

  • Perhaps this blog needs a prevent double posts procedure? ;)

    Opps, also missed a comma from the above.

    WHERE   ses.[program_name] IN (

                               'Microsoft Business Solutions-Navision client',

                               'Microsoft Dynamics NAV client',

                               'Microsoft Dynamics NAV RTC',

                               'Microsoft Dynamics NAV Classic client' )

  • This is just awesome!

    Thank's for this great tip!

    Just I was looking for, for some time now.

    Some of my users tend to open three or four(!) clients at the same time, leaving some other's without availlable licences to work!

    I can't imagine how anyone can actualy use extensivly 3 or 4 clients at the same time!

    Anyway, thank you for the tip. It is great!