For SQL and Navision expert I have a question If i want to give server role in SQL Server to give user login navision what is the best for navision user??? I usually give then System Administrator → all of them and i think it’s a bad idea that i give them role System Administrator so what do you think , about server role in SQL Server i Should give? Thanks for the answer 
Dear Stan,
Yes it’s not a very good idea to give all your users System Administrator rights. You just need to give them normal user rights.
But I can hear that you’re rather new to setting up permissions here. So I’ll suggest that you read this document:
Security Synchronization in Navision 4.0 SP3
and eventually also this:
thanks Eric, those Articles help me a lot By the way there’s no instruction about sql server role i just want to know most secure role for ordinary user → principally not system administrator, heheh
The only SQL role required for non-admin/developer Navison users is Public. This is configured by default when user is created. Admin/Developer users may require additional roles depending on the task performed.
If user only needs to use Navision, public role is more than enough.
Navision uses SQL Application Roles for security. Access is controlled thru the Application Role and not database/Server roles
I mean not database Access (like public or DB Owner) but Server Roles…
Hi Stan,
I believe that Babrown and Nuno Maia already answered that…
An ordinary user only needs to be assigned to the public-role (Database), and not assigned to any server-roles.
No NAV user should have any server roles, and public access to the database is enough.
NAV users that need to do object maintenance (in particular tables) need to have db_owner on the database.
so? in the server roles (sql server) , i don’t have to remark anthing?
cause if i didn’t remark System Administrator, the user in navision couldn’t login
The user (by default) is a member of the database’s Public role and the server’s public role.
Have you sycronized Navision security? There should be no need to have a user as SA. I would review your setup.
it says :
Navision uses SQL Application Roles for security. Access is controlled thru the Application Role and not database/Server roles
when we have a new user, do we manually add the user in SQL server for security and map the user to navision DB before inserting a new user in navision itself?
I am a bit confused…my understand that when there is a new user, we just need to insert their login in navision and give the role then synchronize it.
everything is done throught navision NOT manually add in SQL server
Am I correct or wrong?
regards,
Susan
The Dynamics-NAV security synchronisation process is not able to create or delete logins in Sql Server. Therefore you first need to create a login in Sql Server (public role). After the login is created in Sql Server you create the user in Dynamics-NAV. The user needs, ofcource, to have exactly the same name as the login in Sql Server. Then you need to synchronise security.
Be aware that if you assign in Sql Server the sysadmin role to the login, that then the security synchronisation will fail ! The workaround is then to remove the sysadmin role in Sql Server for that login, then synchronise and then re-apply the sysadmin role in Sql Server for that user. I believe this is a bug in the synchronisation process.
There are two methods depending on the authentication type that you choose for a user:
- For “Database Logins” as NAV calls them, which are “SQL Server authenticated” logins in SQL terms:
You must first create the login outside of NAV using SQL Management Studio, for example (go to Security/Logins right-click and New Login…). You can also do this from Transact-SQL and so on.
Here you choose the “SQL Server authenticated” login type and provide a user name and password. This provides a login to the server but nothing else.
In NAV you then place this same user name into the table under Tools/Security/Database Logins - you then need to place the user into the appropriate roles, but I am not focusing on that part here. Now, if you are using NAV Enhanced security model you need to synchronize using Tools/Security/Synchronize (single or all users). However if you are using Standard security you do not need to do this because NAV will synchronize that user for you. The result of either model is that a SQL “database user” is now created in your current database, which the server-level login refers to.
- For “Windows Logins” as NAV calls them, which are “Windows authenticated” logins in SQL terms:
You do not need to do anything outside of NAV. You place the user domain\name into the table under Tools/Security/Windows Logins (with some restrictions, this can also be a windows group). Then the same NAV security model dependency applies as above, and you likewise get a SQL “database user” for the server-level login.
Now your user can login and use this database, but the extent of the usage will of course depend upon the NAV permissions. Note that the ability to perform the above steps depends on you having the appropriate SQL permission yourself, for example to be a securityadmin role if not a serveradmin. And lastly, you need to have at least one NAV user setup as the SUPER role, which NAV catches and produces an error if its not the case.
Thanks Dean.
This is what understand too… if using window authentication we don’t need to do anything outside Nav.
I have no idea why the support vendor advise me on different process.
Now I get a clear clarification.