Do we know all about Default Dimensions? How to make a dimension Code Mandatory for all the the accounts in Chart of Accounts

Simple. Open Chart of Account, Select all the accounts there, Click on Account > Dimensions > Dimensions-Multiple. Choose the dimensions you want to make mandatory for the accounts and mention 'Code mandatory' in 'Value Posting' field. That's it.

That's the most common answer you will have if you ask somebody how to make a dimension mandatory accross all the accounts in Chart of Account. This common answer is the most common mistake functional consultants make during implementation. Yes, the common answer is not at all right answer.

The above descrbed method of making a dimension mandatory is only valid to make a dimension mandatory for a set of accounts only. Not for entire all the accounts of Chart of accounts.

You may argue - Why not? I am selecting all the accounts from Chart of accounts and making a dimension mandatory for them. And it works.

But think again. Think about a situation where you are creating a new account in Chart of Account.
The dimension you made mandatory for rest of the accounts will not be readily become mandatory for this new account.
So you have to make that dimension mandatory for this new account manually following the same method. If you forget, it can be a disaster waiting for you.

Rather the right mehtod of making a dimension madatory accross all the accounts is much simpler and fullproof (foolproof as well). Use the 'Account type default Dim.' feature. Its available in the Dimension window itself.

Open the Financial Management > General Ledger > Setup > Dimension.

Select The Dimension you want to mandatory accross all accounts

Click Dimension > Account Type Default Dim.

Mention the table no. behind the Chart of Account (That's Table no. 15)

And mention that it's 'Code mandatory' in the 'Value Posting' field.

And this will ensure that the particular dimension is mandatory for entire set of records of te table no. 15 (nothing but the Chart of Account). And in future, any new accounts created in charts of accounts will be automatically included in that mandatory list.

This is the way you can make a dimension mandatory for a entire type of account. For example, the customer account, Vendor Account, Item Master etc.

Have you migrated your Navision to SQL Server 2005 recently

Very recently Nisheel Poddar - IT head RKHS, migrated his Navision server from SQL Server 2000 to SQL Server 2005. Can you imagine what problem he has faced?

His users are unable to change their own password using Tools > Security > Password.

Checked with few more people who has migrated to SQL Server 2005 and surprised to find that everybody is having the same problem and they are solving the problem simply by assigning the dbowner role to their general users.

Dangerous. Don't you think?

Nisheel poddar was not satisfied and searched for solutions. What solutions we found you know?

In SQL Server 2000, we used to give the following roles to users while doing the user mapping to any Navision database:

1. Public (by default this is assigned to all users)

2. db_dataReader

3. db_datawriter

These 3 roles are enough for a generic user to use Navision functionlity. Even this gives you the permission to change your own password using Tools > Password.

Here you mention your old password and assign a new password.

But when you migrate to SQL Server 2005, The same roles assigned to users does not allow them to change to their own password. When your users tries to change it, they will see the following error -

"The following SQL Server error(s) occurred:
15151,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter the login 'nisheel', because it does not exist or you do not have permission.

SQL:{CALL [sp_password](?, ?)}"

When you change your password from Navision client, the stored procedue 'sp_password' is called and the necessary permission to execute this stored procedure was included in 'Public' role of SQL Server 2000.

But the same permission is no longer associated with 'Public' role in SQL Server 2005. And that's why you will have the problem in changing the password. And you are taking a dangerous decision of assigning 'dbowner' role to your users.

To resolve it without assigning the 'dbowner' role, you need to explecitely assign a specific permission to your 'Public' Role (the permission having execute power of Sp_password). Follow the instruction below

1. Follow the standard procedure of creating a new users and mapping them to a your Navision database. While User Mapping, assign the normal roles Public, db_datareader, db_datawriter.

2. Now, assign the permission 'Alter any Login' to your 'Public' role. Select the Server and open its property window.

Select 'Permission' page in left hand pane and select 'Public' role in the right hand pane. Scroll down and find the 'Alter Any Login' permission. Grant this permission to 'Public' Role.

Do remember, you need to be System Administrator to do the above setup.

Now try and see whether your users are able to change their own password or not. They will be able to do that.

This may not be the best possible solution and you are welcome to send your suggestion or any other solution to it.

Do send me your comments and suggestions.