Difference SQL 2000 - Native: roles, db size

rondraeq

Hi two questions for Native to SQL 3.7:

- Database size in SQL is roughly 2/3 (29 GB) compared to Native (45 GB). What would explain this difference?



- Security Roles linked to a company seem more strict in SQL

Example: Item Card, in Native database Access was granted based on company specific and a few global Role permissions.
In SQL environment opening the Item Card give the message there are no permissions to read the "Item Ledger Entry". It is easy to add this to the roles, my question is why is this behaviour different in SQL compared to Native?

kine

1) Size - on SQL just Indexes for keys marked as MaintainSQLINdex=True are created. On Native Server all keys are created.
2) Size - if you have created the SQL from NAV Backup and you are creating this DB with the original, do not forget that the original is not optimized but the new one is, because the indexes were created from scratch and there is no defragmentation etc.
3) On MS SQL the permissions are more strict if you are using Extended security model. Change it to Standard model - it is much better because the permission synchronization process will not need so long time to run.

rondraeq

Thanks for your reply Kine, I am surprised by the size difference between the SQL and Native database.

About standard security model, how is this set in SQL 2000 (Nav 3.7), there is no Advanced tab at alter database.

DenSter

3.7 did not have the choice of security model, that was not until 4.0 SP2 I believe

rondraeq

Thanks for your reply Denster, I believe so too, considering the lack of information I was able to find on the subject.

Is there a way to adapt SQL 2000 to be less strict so security roles and permissions work similar to Native 3.7? Or is adjusting the roles en permissions the way to handle it?

Working with a setting like in point 3 in Kine's post is appealing.

kine

ah, I forgot the NAV version you are using... than it means you are already using the "standard" security model... 8)

Still, on the MS SQL sometime NAV needs the wider permissions because if NAV wants to check if some table is empty, than you need to have permissions to read that table. In native server, NAV knows that the table is empty and after that it is not checking the permissions for reading the table...

kriki

kine wrote:

3) On MS SQL the permissions are more strict if you are using Extended security model. Change it to Standard model - it is much better because the permission synchronization process will not need so long time to run.

You don't even need to do it. I never did it and all works fine.

DenSter wrote:

3.7 did not have the choice of security model, that was not until 4.0 SP2 I believe

4.00SP3.

kriki

[Topic moved from Navision forum to SQL General forum]