This is regarding Dynamics NAV 5.1 and SQL server 2005 user creation. I followed the following method in creating user logins.
Created the relevant user in SQL server 2005
Security>Logins>Created User (For example: John) >User Mapping>Selected database (user mapped to the login)>DB role membership>Public.
Created the user in Dynamics NAV and synchronized the logins.
Although I can login to the system, depending on the access that I have given to this user; it is still asking for the permissions in the relevant table. Though I have given the relevant permission to the relevant table; the message keeps on appearing.
But when I give the db_owner; DB membership role to the user in SQL server this solves the problem.
My concern is, in an actual implementation we can’t give the db_owner as a role in SQL. Because then the user can make changes to the DB.
Since we are hoping to use the windows authentication; we are expecting to use active directory. I just want to know how I can give access to the users by using Active Directory and by only giving the “public” role.
Also I would like to know in the database login environment how to solve this problem; as we have to test the users in the database server authentication before setting it in the active directory. Appreciate your comments and input regarding this.
How exactly did you perform the step number 2 listed?
How did you “give access to the user?”
Only under very rare circumstances you need to change permissions of objects.
The users are windows users. The process we use to great them in Navision is to add the user, that creates it as a user in the db server and the the login is synced. Should they be done the other way around. All of the users are assigned with the All permssions. Below is some more system information
Database: SQL 2008 Ent
Navision 5.0 SP1 - upgrade from 4.0 RC The database is in a 5.0 compatible mode but has never been upgraded to a full 5.0 database.
Windows 2003 AD
Please let me know what other information would be helpful.
What I mean is… you added the windows login to NAV, then you assigned roles to those users, making sure that those roles have the permissions that they are supposed to have and THEN you synchronized the users?
Just adding the windows login in NAV should be enough, the synchronization process should add them to SQL Server.
… but you should also consider the used “NAV Security Model” - I recommend to use “Standard” (if possible) as here the “synchronization” happens MUCH faster. As with STD no user-specific “Application Roles” are generated, it could be assured that there is no issue when transforming NAV rights to SQL rights.
You are correct. We add the Windows Users to NAV, assign the roles, and then sync. The problem is that when we do we still get an error if the users are not set as db_owerns. I am getting the exact error and will post it later today.
In one of the NAV releases, the Property Store table was not included in the All role - by mistake. Can you check that this is included there and if not add it to the All role, with a direct Read permission. Then synchronize again if you are still using Enhanced security (you can do this for a single login and try it with just that login, as a test, before synchronizing all).
The Property Store table is not included in the All role. I am going to make the adjustments later today and test. I will let you know the results asap. Thanks for the help.
Please, if any of the replies to your post solved your problem, then please click on the “Verify solution” next to that post. This way we can see that your post is closed and you got the help you needed. If you found another solution to your problem, then please, out of courtisy to the members who helped you here, post the solution here.
Dean McCrae’s solution worked for me. I have to review my security for my all users settings. I didn’t pose the question so I don’t know how to Verify the solution. Please let me know how and I will gladly do it.
