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 + ', '''''
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;
SET NOEXEC OFF
SET NOCOUNT OFF
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.