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 ):
create procedure [sp_$ndo$loginproc]@appname varchar(64) = NULL,@appversion varchar(16) = NULLasbegin-- do whatever you want to doendgo
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:
create procedure [sp_$ndo$loginproc]@appname varchar(64) = NULL,@appversion varchar(16) = NULLasbegin
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 endendgo
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:
declare @max_logins intset @max_logins = 1select @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")
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:
GRANT VIEW SERVER STATE TO PUBLIC
How should I know what possibly could be wrong here?
Please, just discuss this issue in any NAV forum.
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.
I suggest to discuss this further in some internet forum like MIBUSO.com and/or DynsmicsUsers.net ....
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
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.
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 ... !?
Thank you so much for this article.
Do you know how to Prevent multiple Logins in NAV 2013 RTC?
Please help. Thank you. :)
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!