NAV 2013 - 2018 Access script

Due to recent refurbish of our development environment, we all got new user accounts to develop NAV.

Of course I had to add all the users to the databases, and becuse I am a lazy boy (not the chair, just describing my characteristics), I started to revise my old'n'good Access Script to databases.

I ended up merging all of the different versions of the script to one that will rule them all. I also had to add some error handling to prevent script from running if/when an user is not found. This I did because I do not like to see a lot of errors in my output windows.

The first problem was how to find out what version of NAV the actual database is. I found out that there is a table dbo.$ndo$dbproperty table has a field "dbversionnno" which has different value in each databases. This number tells also the CU level of the database.

Since I need only the major version of the database, I just restored the RTM versions of each NAV release CD's, and checked the versions:

		WHEN @dbversionno >= 70200 and @dbversionno < 70720 THEN 60 --'2013'
		WHEN @dbversionno >= 70720 and @dbversionno < 71040 THEN 70 --'2013R2'
		WHEN @dbversionno >= 71040 and @dbversionno < 80190 THEN 80 --'2015'
		WHEN @dbversionno >= 80190 and @dbversionno < 91470 THEN 90 --'2016'
		WHEN @dbversionno >= 91470 and @dbversionno < 100550 THEN 100 --'2017'
		WHEN @dbversionno >= 100550 THEN 110 --'2018'

The next problem was how to create a dynamic SQL sentence to insert a record with variable columns into a table with same name. Getting the syntax right took me a very long time, since alas, I am no master in T-SQL. With some help from a colleague I managed to make it just right:

DECLARE @UserGUID uniqueidentifier
DECLARE @strInsert1 nvarchar(max);
DECLARE @strInsert2 nvarchar(max);
DECLARE @ParmDefinition nvarchar(max);
...
	-- Create User record
	SET @strInsert1 = 'INSERT INTO [User] ([User Security ID], [User Name], [Full Name], [State], [Expiry Date], [Windows Security ID], [Change Password], [License Type]'
	SET @strInsert2 = 'VALUES(@UserGUID1, @UserID1, '''', 0, ''1/1/1753'', @StringSID1, 0, 0'
	SET @ParmDefinition = '@UserGUID1 uniqueidentifier, @UserID1 varchar(100), @StringSID1 varchar(238)'

	IF @NavVersion >= 70 BEGIN --add 2013R2
		SET @strInsert1 = @strinsert1 + ', [Authentication Email]'
		SET @strInsert2 = @strinsert2 + ', '''''
	END;
...
	SET @strinsert1 =  @strinsert1 +') ' + @strinsert2 + ') '
	EXECUTE sp_executesql @strinsert1, @ParmDefinition, @UserGUID1=@UserGUID, @UserID1=@UserID, @StringSID1=@StringSID;

Finally I added error handling if the nav version is not found or recognized:

IF @NavVersion IS NULL BEGIN
	RAISERROR('No NAV version found for databaseversion %i', 10, 1, @dbversionno)
	set noexec on;
END;
...
SET NOEXEC OFF
SET NOCOUNT OFF
GO

 

This has been very entertaining to learn T-SQL again, and trying to figure out how each NAV versions actually differ from eachother. Luckily I could take the time for this journey!

Please find attached a versatile script to add an AD user to any NAV 2013 - 2018 databases.

Comment List
Anonymous
Related
Recommended