Super Roles In SQL Server 2005 WITH MS Dynamics NAV SP3

The Super role of Microsft Dyanmics NAV contains all the permissions for the objects and system related activities in Microsft Dyanmics NAV. But when the same super role is being alloted to a user is SQL Server 2005 then you will notice the following scenarios:
Case -1:
The database is being restored and users are defined in the SQL Server 2005, and the same users are defined in the database logins in MS Dynamics NAV and the security is synchronized. Lets take two users user A and User B where user A is the dbo owner/sysadmin in SQL Server 2005 and in Navision the role assigned to the both the user A & B is SUPER. After the roles are assigned and the security is synchornized you wioll noticve that both the user/s A and B are able to acess all the object in NAV SP3(includes customized objects). Well till now you will feel that the super role in MS Dynamics NAV SP3 proprietory database is same as that of the NAV database in SP3.

Case-2:
Create a new object (table in which the client license permits to access the table say the obhect id is 50,000). Now import the object into the NAV Dtabase in SQL Server 2005. Now login to the system using the User A. You will notice that the user is able to access the table 50000. After you have verfied with the permission to the table of 50000 with the User-A, login using the user -B. Go tO the Object designer and try to run the object. The system will say that you donot have permission to the Object 50000.

Now you can see that both the users have been assigned the SUPER role but one of the user which not the dbo owner in SQL Server 2005 will not be able to have the acess. In SQL Server 2005 with NAV SP3 everytime you import a object ensure you run a Security Synchronize, as even the SUPER role (which is not the dbo owner/sysadmin in SQL SERVER 2005)will not be able to gain access to the objects.

SQL indexes rebuilt For Navision/MS Dynamics NAV

During the implementation of MS Dynmics NAV with the SQL server option, for the better performance of the system, the indexes needs to be rebuilt for the MS Dynamics NAV(Formerly known as Navision) tables. You can use the following SQL Transaction rebuild indexes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
CREATE UNIQUE CLUSTERED INDEX "CompanyName$TableName$0" ON
"DatabaseName"."dbo"."CompanyName$TableName" ("Field1InPrimaryKey”,”
Field2InPrimaryKey”,…) WITH DROP_EXISTING
CREATE UNIQUE NONCLUSTERED INDEX "$SecondaryKey1" ON "DatabaseName "."dbo"."
CompanyName$TableName" ("Field1InSecondaryKey1","Field2InSecondaryKey1",…) WITH
DROP_EXISTING
CREATE UNIQUE NONCLUSTERED INDEX "$SecondaryKey2" ON "DatabaseName "."dbo"."
CompanyName$TableName" ("Field1InSecondaryKey2","Field2InSecondaryKey2",…) WITH
DROP_EXISTING
…
IF @@TRANCOUNT > 0
COMMIT TRAN

You must schedule the rebuilt of indexes for a table based on:

- Whether or not records can be deleted or modified in the table.
- Whether or not records are inserted sequentially in the index.
- The number of indexes (keys) in the table.
- The number of records in the table.

This will help you in rebuilding the indexes and provide you a better peformnace of NAV with the SQL server option.

Shrink LOG file in SQL for Navision/NAV

Sometimes the biggest hurdle with MS Dynamics NAV with SQL implementation is maintaining the transaction log. It goes bigger and bigger, the users starts feeling the system is going slow and a headache for the system administrators.

The way forward is simple:-SHRINK THE LOG FILE

Follow the steps given below to shrink the same.

1. Open the enterprise manager and connect to the db
2. Open the query analyser and issue the following commands in the SQL editor/Query analyser

DUMP TRAN [dbname] with NO_LOG;

where [dbname] is the database name

3. Execute the query, this execution would show the query successfully executed

4. Right click on the database now and select Shrink. Shrink the log file now to your desired size.

Note: If you simply try to Shrink the log file without issuing the command then the application is unable to shrink the log file and you will still face the issues.

Hope this topic proves helpful to you.