Posted on

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 functionality. 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 procedure ‘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 explicitly 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 to 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.

Reblog this post [with Zemanta]
Posted on

Do things differently

To start our discussion, let’s take a simple topic –

Few months back, in one of Orkut forum somebody had posted a problem as follows:

‘I have to pick out a digit from a code field. E.g. Code is ‘50123AD786′ n I have to select 7th character from this field How can this be done…’

You all know it’s simple and the common way to achieve this is:

Copystr([ur code variable],[position, say 7 for 7th position],[length, here for u its 1])

We have designed a new form here. Taken a new global variable called ‘Name’ of data type Text and inserted a text box to display the variable.

We also have written a single line code as per the above syntax to display the 7th character of the ‘Name’ in a message box.

Now, can we do it in not so common way?

Yes, we can if we know that a text string in Navision is nothing but an array of characters (literally). So here ‘Name’ variable is nothing but an array of its length and we can fetch out the 7th character of it by simply referring it as Name[7]. To validate it, let us change the code in the ‘OnPush’ trigger of the Button as below:

Message (FORMAT (Name [7]));

Why we needed a FORMAT command? Just because the message box can only display text not character.

Here I entered ‘Snehanshu Mandal’ in the text box and clicked on the button ‘Click me’. It’s displaying the 7th character of my name ‘s’.

Don’t you think it’s interesting?
Posted on

How can we make working on Navision a FUN?

Hi friends! How are you doing?

Last month, I completed 6 ½ years working in Navision and was stumped by one of my junior’s question – Don’t you get bored while working with Navision after so many years? Big question.

People are getting bored very fast nowadays. But why? You get bored when –

1. Work is monotonous
2. Work can be monotonous when you know everything and there is nothing new left to learn
3. ‘Hey, I am doing the same form designing, report designing and data port designing’

Besides the above points, you will find plenty of reasons of why people are getting bored in Navision.

I think the answer lies in the approach you have towards Navision. Whether we are trying to solve a problem too technically or we are having a holistic approach to solve a problem? Again to have a holistic approach towards a problem, you need to be little experienced on Navision or you have a fair idea about Navision functional things. So the whole issue is little paradoxical.
Whatever may be the reason, here we are not to discuss the problem. Rather we are here to try & make working on Navision a little more enjoyable. And to make it enjoyable, the best thing is to do something new in Navision every day. Mind it, you can do something new only when you do not know everything but you know something of Navision to start with.

I am not here to teach you something new in Navision rather together we will search new things in Navision. Every day, we will discuss about one topic and if you feel interested, you are welcome to suggest me the future topic or contribute a new discussion. You are welcome to send the problem you are having in your implementation and together we will try to evolve most reasonable solution of it. When so many brains will work on the problem, definitely we will be able to achieve most suitable solution for the problem
So let’s wish ourselves happy learning of Navision.