NAV 5 userrights through script in SQL Server 2005

hvdhoeven · 2008-03-01

Hi,

For an educational setting we would like to enroll users to NAV 5 databases and companies through a script in SQL when adding the users. We have decided to user database logons (not windows logon)

We have about 50 databases with 40 companies each and would like to have a way of minimizing the effort of adding users and refreshing databases (after each period of 10 weeks). We will name the users R301S01 ... R301S40, R302S01... R301S40 and so on, where R30x is the name of the database.

We're trying to set up an initial user management.

Starting position is (I think) a NAVdatabase with only one superuser, that is already known in SQL. In this database there are 40 companies. Each user (student) needs his/her own company. I only sync the superuser in NAV.

So we would like to have a script that addresses the following issues:

- add user with password in SQL with usual rights in NAV (db_owner?)
- add the same user in the NAV database in a specific database/company
- give the user in NAV in that database/company roles 'all' and 'super(data)'
- make sure that the users are synchronized between NAV and SQL

First question: Is this possible?
Second question: How can we achieve this?
Third question: Do you hav an example of the script? Please?

Thanks very much for your assistance.
My students (Dutch university of applied science with little money for implementations...) will be very happy when they can work with NAV!

Hans van der Hoeven
The Netherlands

garak · 2008-03-01

first all your questions are possible with an little sql script.

But don't give them all the permission db_owner. public is better ;-)

synchronized between NAV and SQL <- u will use the standard seurity model oder the enhanced model ?

To create this scipt is very simple.

First creating user on Server and grant to an group (public)
http://msdn2.microsoft.com/en-us/library/ms173463.aspx

After this, you can insert the needed records in the Navision tables (Database Login, Member of)

these you do with the sql statement
for insert records -> insert into
for modify records -> update

I will not give you the hole solution, but i show you the way to find an solution ;-)

To see which statements are send from Navision to SQL Server, when adding an new database user and his permission, you can use the SQL Profiler.

Regards

rvduuren · 2008-03-01

Hello Hans,

Yes it´s possible to create NAV (database users) and SQL users from Dynamics NAV Client using ADO.

Here´s my code ( NAV Client 3.70B SQL2000, but it will probably work on NAV 5.00 too, SQL2005 not sure..).

It´s an out of the box working example ( uses 'Microsoft ActiveX Data Objects 2.8 ).

OBJECT Form 90909 Create NAV/SQL user
{
  OBJECT-PROPERTIES
  {
    Date=;
    Time=;
    Version List=;
  }
  PROPERTIES
  {
    Width=5940;
    Height=4620;
  }
  CONTROLS
  {
    { 1000000000;TextBox;3630 ;220  ;1700 ;440  ;SourceExpr=Sa }
    { 1000000001;Label  ;220  ;220  ;3300 ;440  ;ParentControl=1000000000 }
    { 1000000002;TextBox;3630 ;770  ;1700 ;440  ;SourceExpr=SaPassword }
    { 1000000003;Label  ;220  ;770  ;3300 ;440  ;ParentControl=1000000002 }
    { 1000000004;TextBox;4070 ;1540 ;1700 ;440  ;SourceExpr=UserName }
    { 1000000005;Label  ;660  ;1540 ;3300 ;440  ;ParentControl=1000000004 }
    { 1000000006;TextBox;4070 ;2090 ;1700 ;440  ;SourceExpr=UserPassword }
    { 1000000007;Label  ;660  ;2090 ;3300 ;440  ;ParentControl=1000000006 }
    { 1000000008;CommandButton;3520;3850;2200;550;
                                                 Name=Create;
                                                 OnPush=BEGIN
                                                          Addlogin(UserName,UserPassword,Sa,SaPassword);
                                                          Adduser(UserName,Sa,SaPassword);
                                                          AddNAVLogin(UserName,Name,Role);
                                                        END;
                                                         }
    { 1000000009;TextBox;4070 ;2640 ;1700 ;440  ;SourceExpr=Name }
    { 1000000010;Label  ;660  ;2640 ;3300 ;440  ;ParentControl=1000000009 }
    { 1000000011;TextBox;4070 ;3190 ;1700 ;440  ;SourceExpr=Role }
    { 1000000012;Label  ;660  ;3190 ;3300 ;440  ;ParentControl=1000000011 }
  }
  CODE
  {
    VAR
      Sa@1000000000 : Text[20];
      SaPassword@1000000001 : Text[20];
      UserName@1000000002 : Text[20];
      UserPassword@1000000003 : Text[20];
      Name@1000000004 : Text[30];
      Role@1000000005 : Code[20];

    PROCEDURE Addlogin@1000000013(iNewUserName@1000000000 : Text[20];iNewPassword@1000000005 : Text[20];iSAUserName@1000000009 : Text[20];iSAPassword@1000000008 : Text[20]);
    VAR
      lADOCommand@1000000004 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000507-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Command";
      lADOConnection@1000000003 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
      lADOParameter@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{0000050B-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Parameter";
      lActiveConnection@1000000001 : Variant;
      lServerName@1000000007 : Text[100];
      lDatabaseName@1000000006 : Text[100];
    BEGIN
      IF ISCLEAR(lADOConnection) THEN
        CREATE(lADOConnection);

      lServerName := GetServerName;
      lDatabaseName := GetDatabaseName;

      lADOConnection.ConnectionString := GetConnectionString(lServerName, lDatabaseName, iSAUserName, iSAPassword);
      lADOConnection.Open;

      IF ISCLEAR(lADOCommand) THEN
        CREATE(lADOCommand);

      lActiveConnection := lADOConnection;
      lADOCommand.ActiveConnection := lActiveConnection;
      lADOCommand.CommandText := 'sp_addlogin';
      lADOCommand.CommandType := 4;
      lADOCommand.CommandTimeout := 0;
      lADOParameter := lADOCommand.CreateParameter('@loginname', 200, 1, 20,iNewUserName);
      lADOCommand.Parameters.Append(lADOParameter);
      lADOParameter := lADOCommand.CreateParameter('@passwd', 200, 1, 20,iNewPassword);
      lADOCommand.Parameters.Append(lADOParameter);
      lADOCommand.Execute;

      lADOConnection.Close;
      CLEAR(lADOConnection);
    END;

    PROCEDURE Adduser@1000000014(iNewUserName@1000000009 : Text[20];iSAUserName@1000000007 : Text[20];iSAPassword@1000000000 : Text[20]);
    VAR
      lADOCommand@1000000004 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000507-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Command";
      lADOConnection@1000000003 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
      lADOParameter@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{0000050B-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Parameter";
      lActiveConnection@1000000001 : Variant;
      lServerName@1000000005 : Text[100];
      lDatabaseName@1000000006 : Text[100];
    BEGIN
      IF ISCLEAR(lADOConnection) THEN
        CREATE(lADOConnection);

      lServerName := GetServerName;
      lDatabaseName := GetDatabaseName;

      lADOConnection.ConnectionString := GetConnectionString(lServerName, lDatabaseName, iSAUserName, iSAPassword);
      lADOConnection.Open;

      IF ISCLEAR(lADOCommand) THEN
        CREATE(lADOCommand);

      lActiveConnection := lADOConnection;
      lADOCommand.ActiveConnection := lActiveConnection;
      lADOCommand.CommandText := 'sp_adduser';
      lADOCommand.CommandType := 4;
      lADOCommand.CommandTimeout := 0;
      lADOParameter := lADOCommand.CreateParameter('@loginname', 200, 1, 20,iNewUserName);
      lADOCommand.Parameters.Append(lADOParameter);
      lADOCommand.Execute;

      lADOConnection.Close;
      CLEAR(lADOConnection);
    END;

    PROCEDURE AddNAVLogin@1000000004(iUserName@1000000001 : Text[20];iName@1000000003 : Text[30];iRole@1000000005 : Code[20]);
    VAR
      lUserRec@1000000000 : Record 2000000002;
      lMemberRec@1000000004 : Record 2000000003;
    BEGIN
      CLEAR(lUserRec);
      WITH lUserRec DO BEGIN
        VALIDATE("Gebruikers-ID",iUserName);
        lUserRec.Naam := iName;
        INSERT(TRUE);
      END;

      WITH lMemberRec DO BEGIN
        VALIDATE("Gebruikers-ID",UserName);
        VALIDATE("Rol-ID",iRole);
        lMemberRec.INSERT(TRUE);
      END;
    END;

    PROCEDURE GetConnectionString@1000000000(iServerName@1000000000 : Text[100];iDatabaseName@1000000001 : Text[100];iUserID@1000000002 : Text[30];iPassword@1000000003 : Text[30]) : Text[1024];
    BEGIN
      EXIT('Driver={SQL Server}; Server='+iServerName+'; Database='+iDatabaseName+'; Uid='+iUserID+'; Pwd='+iPassword+';');
    END;

    PROCEDURE GetDatabaseName@1000000015() : Text[100];
    VAR
      lSessionRec@1000000000 : Record 2000000009;
    BEGIN
      lSessionRec.SETRANGE("My Session",TRUE);
      IF lSessionRec.FIND('-') THEN
        EXIT(lSessionRec."Database Name");
    END;

    PROCEDURE GetServerName@1000000017() : Text[100];
    VAR
      lTemp@1000000000 : Text[1024];
      lCounter@1000000001 : Integer;
    BEGIN
      lTemp := CONTEXTURL;
      lCounter := STRPOS(lTemp,'servername=');
      IF lCounter > 0 THEN BEGIN
        lTemp := COPYSTR(lTemp,lCounter + 11,999);
        lCounter := STRPOS(lTemp,'&');
        EXIT(COPYSTR(lTemp,1,lCounter - 1));
      END
      ELSE
        EXIT('');
    END;

    BEGIN
    END.
  }
}

Let me know if this is what you mean..

No hard feelings.. ..but students, with low budget. Why not use an Open Source alternative, like Open Bravo or something..

hvdhoeven · 2008-03-01

rvduuren wrote:

No hard feelings.. ..but students, with low budget. Why not use an Open Source alternative, like Open Bravo or something..

Thanks rvduuren!
We use NAV with a special MS Academic Alliance Licence.
The implementation is the most costly part of all.
Now we're using Navision 3.70 with C/SIDE, but will be migrating to 5.0 with SQL in a month. So I'm exploring the different paths to find the best one.

Other tips & tricks are welcome (also in Dutch...)!
You can mail me at jpm.vanderhoeven(at)avans.nl

rvduuren · 2008-03-01

Hello Hans,

Usefull link before migrating to NAV SQL::> Changing Alphabets - Moving from C/SIDE to SQL - by: Mark Brummel

If you want I can also mail you the FOB of my previous post..

Waldo · 2008-03-01

rvduuren wrote:

Hello Hans,

Usefull link before migrating to NAV SQL::> Changing Alphabets - Moving from C/SIDE to SQL - by: Mark Brummel

If you want I can also mail you the FOB of my previous post..

May be you can upload is as a download on mibuso?

rvduuren · 2008-03-02

@ Waldo

Waldo wrote:

May be you can upload is as a download on mibuso?

Good idea! I'm considering merging the code in a wizard, for easy use. I'll try and post it as a download soon..

NAV 5 userrights through script in SQL Server 2005

Comments

Categories