DB access in Nav only possible with db_owner role in SQL2005

AndreasD

Hello,
I realized that a user can only open the database with the Nav40SP3-Client if he/she is given the role db_owner for this database in SQL Server 2005 (in db in SQL Server 2005: right click the user name under security, users, => Properties)
How can that be?
Why does not db_accessadmin or db_datareader suffice? Why is any role necessary if Public has been given access thru which the user should gain access?

Could someone shed some light on this?

Thank you
Andreas

bbrown

DB_Owner is NOT required for access to NAV. In fact no other role membership beyond Public is required for normal users. Security is handled thru application role(s) which are built by the security syncronization process in NAV

NOTE: DB_Owner is required for users doing development and selected Admin task

DenSter

Additional note: the only development task that requires db_owner is for table changes, as that is the only type of change that affects SQL Server.

Karenh

There is a database option in Navision (NAV) that db owners only can use the database.

I think you see it on one of the tabs in Alter Database.

bbrown

Karenh wrote:

There is a database option in Navision (NAV) that db owners only can use the database.

I think you see it on one of the tabs in Alter Database.

The purpose of that setting is to disable access for normal users. There may be a time when you want to run a maintenance process and don't want users logging into the database.

AndreasD

Hello,

thank you.
However, this does not seem to be true.
First: The option for "db owner only" is NOT checked.

In SQL Server 2005 Management Studio I go to the database in question.
Then: Security, Users, The user in question
Rightclick, Properties
This opens the window database User for the user in question.
The upper part is owned schemas, the lower part is database role membership.
If I take out the checkmark before db_owner in the lower part of the window then – when the user tries to open the database thru the Navision client – he gets the message
The combination of user ID and password entered is invalid. Try again.

As soon as I set the check mark before db_owner he can access without any changes in the passwords.

If this checkmark is not necessary, what then is wrong here?
What permissions have to be set for public?

Thank you
Andreas

bbrown

You do not need to assign any permissions to public. The defaults are all that is required.

Have you setup the security in NAV and sycronized? See section 4.1 of the w1w1isql.pdf document that is in the DOC folder of the CD.

AndreasD

Hello,
the issue was indeed the synchronization.
First I created a new db in sql server. Then I imported a backup thru the Nav client. Of course the db had many users that did not exist in sql server so that synchonization failed. It is too bad that the only option for synchronization from Navision is for all users. They should have an option to synchronize a single user!
The error message The combination of user ID and password entered is invalid. Try again. was due to the fact that the synchronization from Navision is to the user name in sql server not to the login in sql server. So to be on the safe side it is best to have user and login named the same.

Also the user does get the default schema dbo although he does not become member of the role db_owner.
Is it normal that a new user gets the dbo as default schema? Shouldn't a new schema be created under the users name?

Then it is interesting to note that obviously you can get around the need of Synchronize all by giving the user the role db_owner in sql server 2005. The rights in Navision will still be restricted thru the configuration in Navision (at least it seems to me to be that way).

Finally the synchronization creates many funny named schemas and application roles in SQL Server although I deleted the non-existing users in Navision beforehand. The names go s.th. like
$ndo$ar$01B6C6194E6C56440971CDA47AD26F0F
I assume these are application roles which get their own like named schema in sql server?
It looks kind of strange, doesn't it?

Regards

bbrown

AndreasD wrote:

... They should have an option to synchronize a single user!
...

Enhanced Security gives you this option. Don't consider using it. Losing the ability to sync single users is a small trade-off compared to the other issues it will create.

kriki

[Topic moved from Navision forum to SQL General forum]