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.
"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.
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.
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
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.
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.
When you make someone DB_Owner you are effectively making them administrator of that database. They have unrestricted access to all objects and all data. Even though you may restrict them from access certain data with in NAV, as a DB_Owner they could simply take Excell with an ODBC connection and access (and change) any data in the system.
For the one table, I have a bad feeling that they have done this on most of their tables. I know atleast two so far.
I just wanted to clarify the $ndo$shadow isn’t that only related to Navisions? I’ve never seen this in a standard database. If that is the case then I can’t see how you could access the tables through Excel and do anything unless your Windows Authentication allowed you to? Though I’m not 100% sure how Navision security works with relations to Windows Authentication…?
Well if its an official add-on I would report this to Microsoft. If its custom code developed for you, then I can not see how its an issue just get them to fix it.