BPSoftware.com

While working on a report based on the results of a complex stored procedure I  encountered what I considered to be a strange behavior.  I noticed that when the stored procedure was executed directly the results were returned in seconds and they were returned in several minutes when executed from the report.

After further looking into the execution I realized that the parameters in the report were “prefetched” and analyzed where they were used in the procedure. which took some time.  I made a few small changes to the procedure and had favorable results.  I declared local variables, assigned them the values that were passed to the procedure and used the local variables within the procedure for filtering the data.  The report results were returned in the same interval as executing the stored procedure directly.

 The DBINFO Database Console Command (DBCC) lists some valuable database information.

DBCC DBINFO (<databasename>) WITH TABLERESULTS

A few results of note:

dbi_dbccLastKnownGood – The last date DBCC CheckDB successfully ran without error

dbi_crdate – The database creation date

dbi_dbid – Internal database ID number

It may be necessary at times, for example due to software data maintenance, to prevent all but a few users from logging into a Microsoft Dynamics Nav database.

There are a few options available for accomplishing this task.  If Database Authentication is used, then setting an expiration date will prevent the users from logging in.  This can be a tedious task if you have several hundred users.  If you’re using Windows Authentication you can remove the user from the Windows List or disable their Windows account (same applies to Windows groups).  This also presents an issue if you have a number of users and/or an elaborate set of security roles.  Removing the users is an impractical choice that requires the users to be added and setup again.  Disabling the Windows login is not a reasonable option as it also disables a user’s access to other resources.  Another common thought option is to put code in Codeunit 1 - ApplicationManagement.  This sounds logical, but in order for a user to run Codeunit 1 they have to be logged in.

There is another option, which only works for Dynamics Nav with a SQL database.  You can create a stored procedure sp_$ndo$loginproc. This stored procedure is executed as a user is “logging” into the Dynamics Nav database.  If you raise an error in this procedure the user will not be able to open the database with the Nav client under both Database or Windows authentication.

1. Create a table, from within Nav, called _UserLockoutAllow.  Set DataPerCompany to No and add a field, Code 20, “User ID”.
2. Create the sp_$ndo$loginproc stored procedure:

   CREATE PROCEDURE [dbo].[sp_$ndo$loginproc]
   @appname VARCHAR(64) = NULL,
   @appversion VARCHAR(16) = NULL
   AS
   BEGIN
   	DECLARE @cnt integer
   	SELECT @cnt = COUNT(*) FROM [_UserLockoutAllow];
   	IF (@cnt > 0)
   		BEGIN
   			IF Not (Upper(SUSER_SNAME()) IN (SELECT [User ID] FROM [_UserLockoutAllow]))
   				RAISERROR ('The system is currently unavailable for scheduled maintenance.', 11, 1)
   		END
   END	
   
   
   GO

Adding User IDs to the table will only allow those users to access the database. If the table is empty then everyone can login. Also note that if you add a User ID to the table all logged in users will receive the error and be logged off if their User ID is not allowed to login.