We’re going to use windows login to MBS 4.0 instead of the database login procedure. We have installed the extended storedprocedure ‘xp_ndo_enumusergroups’, granted public exec rights to it, etc. Now here’s the tricky part - it seems that the login procedure works as far as to the MBS security level (we obviosly login in to windows succesfully, connect to the server hosting MBS and gain access to MS SQL Server) - here MBS fails with the message (freely translated from danish) : ‘You don’t have access rights to read the table Object. Contact the sysadmin…’ If I give the user login dbo rights or add him to the dbsysadmin user group (in SQL Server) the login is succesfull without any problems, but we can hardly give every user admin rights to the database. If I give the group ‘public’ read access to the Object table, another table fails - if I manually give read access to every table (all the ones that are common between Companies) I think I can solve the problem, but this seems as an odd solution - and too much work every time we would like to add a new database server (gaining new customers). Does anyone know of a solution to keep the users in the public group and still gain access to MBS without modifing the access rights to the tables? Syncronising the security from MBS doesn’t help.
There is no intention to give any special permissions in SQL for 4.0 (except db_owner to your administrators). All permissions for regular users are granted using the Tools/Security/Synchronize but it must be done after any change to security. If this is not working for you then there is a bug here. If you remove the public read access to the Object table - to get the Object table error again - then do Synchronize from Navision, then try again does it still come up with an error on the Object table?
I tried this (again) and it still fails with the access denied to the Object table.
As you say setting db_owner solves the problem, what are the major issues with setting db_owner for a normal user? The Navision permission will still come in to force and work. I am a little confused as to what db_owner actually allows etc. I know that you cannot change database structure IE import Fobs with new fields without it!
db_owner would allow the user to connect to the SQL Server around MSB (ie. using Enterprise Manager or other) and gain full control over every table, storedprocedure etc. This could potentially be very bad. As a rule of thumb we don’t trust our customers to behave nice - we would like to force them into that.
OK, then it sounds like your Navision security is missing the needed permissions. If you go into Tools/Security/Roles (you’ll have to manage the Danish) and choose the ‘All Users’ role then Permissions, you must ensure that the following tables have been given Read permisson (but no other permissions are necessary). I will shorthand the object ID: 1 Object, 2 User, 3 Member Of, 5 Permissions, 6 Company, 53 Windows Access Control, 54 Windows Login, 61 User Menu Level Actually I believe Company was already in previous versions, but these extra permissions are necessary for version 4.0 for various reasons. This should already be in the Cronus Demo data shipped with 4.0 so I don’t know why you don’t already have these, unless you have upgraded a database from a previous version. Remember to Synchronize security again after these changes. You can then remove any special permissions you granted your user or to public, in SQL Server externally.
To clarify the db_owner usage. As Frederik says, a db_owner user has all rights to all objects in the database he is owner of. So it should be minimized to basically Navision SUPER users. Navision requires the permissions that db_owner has for some specific purposes, the most common being creating and modifying table definitions which require SQL DDL statements. In order to simplify the SQL permissions, db_owner is required for these tasks rather than assigning specific DDL statement permissions to the user. So doing a Navision Restore, or an Import if it requires table definition changes, requires db_owner. For pure data modifications, db_owner is not necessary since Navision can control this for a regular SQL user via its own security system.
i created a user in SQL server and alloted him public role, and then i created the same user in navision 4.0 and assigned him SUPER role. When i want to login into navision 4.0 with the new user created, it gives me error ‘Permision read not granted to Object table’. Any comments what am i missing?
See my answer in your other post.