Hi,
I'm having a problem where a User which is granted SUPER roles in Navision gets an error when doing a transaction.
The following SQL Server error(s) occured while accessing hte PW Inventory - Lot table:
1088, "42000", [Microsoft][ODBC Sql Server Driver][Sql Server]Cannot find the object "3PLink_Database43_SQL.dbo.Coast 2000 Terminals Ltd_$PW Inventory - Lot" because it does not exist or you do not have permissions.
SQL: SET IDENTITY_INSERT "3PLink_Database43_SQL","dbo"."Coast 2000 Terminals Ltd_$PW Inventory - Lot" ON
I know I can post successfully but a few other Users can't. I was under the impression that SQL Server used Application Roles so there should be no connection between the fact that I am a Sys Admin and the rest are Users. If I look at all the Application Roles they all have the same permissions with Delete, Insert, Select and Update all set to Grant on this table. I was trying to figure out a way to map the user to the Application Role but I was not aware of anything. Any help would be greatly appreciated.
Running SQL Server 2005 and Navision 4.0 sp3.
Thanks,
Paul
At first glance it looks like you may be set to Enhanced Security Model.You might want to try in a test environment to use standard.
"SET IDENTITY_INSERT " is a built in SQL permission that is granted to DB_Owner and higher only. You can't assign it. You get this message because you are attempting to insert a non-zero value into an auto-increment field. You need to review and correct your code. Do not assign DBO priviliges to get around this.
Being SA is exactly why you do not get this error. SA is granted this right on all databases. SA rights are not suppressed by application roles.
Ahh that makes sense now. I will have the company look into their code. However is it better to being on Enhanced or Standard security as I have read many different things with this. Also if I do switch it to Standard how do I support the database logins? Do I need to add the Database logins as users in the SQL Database?
Switch to standard. I can't think of a good reason to use Enhanced. I asked MS support if they could give me a scenario where it made more sense to use Enhanced. They were not able to.
I don't get your question about database users. You need to set them up in SQL no matter what security model you are using.
Because I was only using Database Logins for my RF Handhelds I didn't need to synchronize the Permissions hence I didn't need them on the SQL database. This was how it was setup and I really never questioned it, now however I will add them as it never really made sense to me in the first place.
Thanks for the help!
This is the reponse I got from my customer. Could you please let me know if this is exceptable or not.
1Go to the server console and launch SQL Enterprise Manager 2Expand the (local) server > Databases > and the Navision SQL Database 3Click Roles. In the right pane, double-click ‘db_owner’ 4Click Add, then select ‘$ndo$shadow’ and click ok. 5Click Ok in the Database Roles Properties window
No this is not correct. There is NO reason for a normal NAV user to EVER be a DB_owner
This is a coding issue. I see this often when a developer will work in a native DB and deliver code that will be run in SQL. This behavior is quite different between the two. A common oversite is to use Rec.INIT and forget that it does not clear the primary key fields.
That is what I thought, however they are basically telling me that they aren't willing to change all there code as this works perfectly fine on a native database. Is there any risks with doing this for a short term fix? Any security risks?
See my previous post. SQL behaves differently then native in some areas. When developing for a SQL site the testing needs to be done in SQL. If not you set yourself up for nasty surprises like this.
Making everyone DB_Owner is the SQL equivalant of making all your network users members of the administrators group. I strongly advise against this approach.
My understanding with this is that it will only make Navision users a member of hte administrator group. Therefor only when coming through the application does it appear this way but not when they are accessing the database through Windows credentials? Or am I misunderstanding how this works.
pwellar:they are basically telling me that they aren't willing to change all there code as this works perfectly fine on a native database
You are kidding right
Who are "they"? Do you mean the Navision partner that supports them, or a thrid party company that developed the code?
The actual third party company that developed the code, I am sure I can get them to do this over time but they aren't willing to do it in the short term thats for sure.