Navision with SQL option

lzr

If I create a database/windows login in Navision they are synchronized correctly to the sql server. However they get no roles in SQL. Therefore even if they are assigned as super in Navision they still can't do anything...

What is the role the users (ordinary non super users) should have in SQL server? db_owner?

bbrown

Navision uses application roles in SQL to handle user rights. It is not required to setup any SQL roles for normal navision users. There are some exceptions for admin and development users.

In order to create/modify table structure, a user must be a member of the database's db_owner role or higher.

In order to create new databases, a user must be a member of the db_creators role or better.

There are others but basicly users requiring normal everyday access do not need any special SQL roles. Only users that are involved in processes that need to alter the physical database require SQL roles.

This provides a flexible security enviroment, as it allow you to grant a user "super" rights in Navision (meaning the user has all access) but the user has no rights to access the database from outside the Navision client.

lzr

Hmm. I tried this but users could not do anything in Navision, even with super. I only tried with database logins tho.

Will try again, thanks a lot for answer

bbrown

This should work fine for database logins. Try the following steps:

1. Open Enterprise Manager and select Security - Logins.

2. Right click and choose "New Login"

3. type a login name, select "SQL Server Authentication" and click OK. MAKE NO OTHER CHANGES

4. Close Enterprise Manager

The above steps are only required for database logins

5. Open Navision and sign in. The user account you sign in as must be a SQL system admin.

6. Go to Tools - Security- Database logins.

7. Add the user created in step 3 and add the desired roles. Close the forms.

8. Run Tools - Security - Syncronize

lzr

Hmm ok. I did everything except the last step, I thought it would synchronize automatically. Think I have some problems with the synchronization of my sysadmin account.

Anyway another security question: Does the a user with "super (data)" role need the ALL role?

I am studying for my installation exam tomorrow 8)

bbrown

Yes, or another role that grants access to non-table objects. Good luck on the exam.

lzr

Thank you!

kine

Only for MS SQL 2005:

To be able to sync accounts you need to be DB_Owner

To be able to change Password, you need to have access to execute "ALTER ALL LOGIN" (sp_password).

lzr

To synchronize you need to be either sysadmin or db_owner + securityadmin. Not only for ms sql 2005?

Anyway I passed my exam, thanks for your help \:D/

kine

Ok, it seems to me that you are right. But I think that you will have another error message. On MS SQL 2000 you will have "You do not have permissions to..." and under MS SQL 2005 it will raise error like "The user xxx is not existing on MS SQL" and the xxx is some user account another than you are logged in. But the user account is there and if you will synchronize it through "Synchronize Single User" it will be ok.

bbrown

If you are sysadmin (or a member of the system administrator role), you do not need to be assigned to any other roles. Sysadmin is grant all rights on the SQL server.

kine

Of course, I am talking about minimal requirements.