I am trying to put a simple Trigger on the Customer table of Naivison in SQL. (Navisiojn 4.0 on SQL, Both Database & Windows logins) The trigger throws updates & insterts to a customer table in another database on the same SQL server. All users can access this other database, that is not the issue. It all works super if I logon Navision with a Windows or Database login that is a member of db_owner group of the Navision database. But the normal Navision user are only member of public. And as soon an they change a Customer in Navision they get the Error Message “The Combination of User ID and password entered is invalid. Try again” If I add the user to the db_owner group of the Navision database it works fine… Does Anyone who what rights i need to assign in SQL to my public users in order to allow them to run triggers on a Naivsion table? I could create a new Permission group in the SQL Database and assign the users to this one if I knew what permissions to add to it, that would be a good workaround, but to set all users as db_owner is not acceptable 
Best Regards Johan Emilsson UNI2 AB
Hi Johan! Well, I’m just guessing … … with SQL & NAVISION, the NAVISION Userrights are “translated” into SQL Userrights “on runtime” via a stored procedure (xp_ndo_enumuserids or so) and a related DLL. I suppose, that when accessing the trigger, this “translation” doesn’t work. Maybe you can modify the rights of the SQL public role by explicitly permitting access to the customer table?
Hi Joerg, and thanks for your reply. But nope I have tried that. I have tried to create new roles, Application as well as database, and in the roles specifying explicit rights to customer table, hell i even gave full rights to all objects I could that was not clearly a Navision Data table…and it still didn’t work.
When you try to insert a customer - public user - what is the SQL Profiler showing? Any strange statement, maybe? I remember a similar issue with the AutoIncrement feature, requiring a SET IDENTITY_INSERT command which is only available for db_owner, etc. - maybe there’s something similar?
Hi again Joerg, No the SQL Profiler doesnt tell me anything special, I have compared the statements with successfull changes(done by my Navision Login that is db_owner) with Statements done with a normal user (public), and they look quite similar… In the statemests prepared there is no funny things, except for a transaction count > 0 thing that Naivsion uses to check after the whole prepared statement is executed, but that is same for both user statements so it cant be it…
**** …I’m stucked with a thing that ought to be …or atleast I thought so be really simple and smart… - Quote myself : “…Ohh, we jsut put a trigger on the Customer table and woops you have all changed data required in navision there” Guess i have to eat that now…[xx(]
Another guess … In NAVISION, there is the database role $ndo$shadow which is used for that “user-rights-translation”. This role has all permissions to access the NAVISION tables, maybe you need to add the permission of your table you are going to insert new data ?!?
Yeah…Thanks for another good guess, but unfortunally i think, thats valid up to 3.70…since 4.0 Navision generates1 application role per User[Sigh…][Ugh][Duh!][No]… Im not 100% on this and havent gone into details, but it certainly looks like it… And i do not feel like going in and changing 87 Application roles after every Naivison Syncronization…especially when it takes 2 hours to sync the permissions, then another 20 minutes to open each individual application role and change it…
P.S please correct me if im wrong, but this thing has been dicussed a lot, Navision 4.0 generates 1 application role per user, they used to use 1 application role ($ndo$shadow… and via this one interpet the Navision userr rights) - this is what they refer to as the “Enhanced Security” right?
Yes its correct that 4.0 uses one app role per user and prior versions used one app role per database. The simple rule for SQL permissions when you log into Navision is that it checks if you are a db_owner member in the database. If so it leaves your permissions and you get in as a db_owner (you may be further restricted as to what you can do, by the Navision security system which is totally separate). If not, it activates your app role if you have one (if not, you are not a valid user). You get all permissions on your app role and lose any previously granted permissions, although you are still identified in the database as your original login and database user, for auditing purposes for example. The problem with app roles is that they have no permissions in any other database - they assume the guest identify in other databases. So you cannot grant your extra permissions even if you wanted to. The short answer then is that you can only handle this by making the user a db_owner.
Wow thanks… That really sucks, My workaround thenwill be to try if it works if i put the sync table in the navision database itself, just not comfortable in giving access to this database to other systems, but hey …i’ll just cut their accounts rights using a application role :-), juust like Navision! Thanks for all replies!