User permission issue

couberpu

Good afternoon,

My company is running NAV 2009 R2 classic with SQL option on a physical Dell server. The SQL version is 2008 R2 enterprise.
Since the only reason for using enterprise is we need 128GB of RAM, we are looking into the possibility of using SQL 2014 standard with a vm. It can support up to 128GB of RAM and we can also lower the cost running in vm.
I am working on updating existing SQL 2008 R2 to SQL 2014 standard and ran into user permission issue.

1. We purchased a new Dell server with RAID 10 disk array and created a hyper-v host and guest machine for hosting MS SQL 2014 server.
2. I moved all users using sql script from SQL 2008 R2 to this new vm.
3. I then did a sql full backup and restore into the new vm
4. Synced users in NAV security.

Everything seems working but I do realize for some users I have to make them db_owner to avoid permission error message, not on all users but just on some of them.
Is there a SQL command or setup I can do so I only need to assign public role in SQL?

Please help

Thanks,
Couber

bbrown

What permission error are you getting?

Just curious: Why do you need 128 GB bad enough that it justifies the extra cost of SQL Enterprise?

couberpu

Good question.
I started on May this year and it was there already. Not sure how the company got into this mess.
I am fixing it with SQL 2014 standard in VM and it only cost us 'pennies' with new SQL license terms.
The old server has 24 cores and the new server has 32 cores.

That is not an issue here.
The issue is that I do not want to make anyone db_owner but my self . But the thing is I ran into permission issues if I don't. Will standard security setup address this issue?
I already did the sync.

Or I have to make a bad decision to go live and address the issue by totally fixing user permission setup? ](*,) ](*,) ](*,)
In our business, the 'season' started this weekend and I have to go live.

Thanks,
Couber

couberpu

Oh, Sorry about left out the error message.
I will get error message on tables that the company was not license for!

One example will be in sales order form, I will get read permission error on Campaign Target Group table which we were not licensed for.

bbrown

What permission errors are you getting?

It's hard to offer any suggestions if you don't provide some details on the problem.

bbrown

couberpu wrote:

Good question.
...
I am fixing it with SQL 2014 standard in VM and it only cost us 'pennies' with new SQL license terms.
...Couber

I know it's not the issues here, but you've caught my interest. I don't see the SQL licensing cost being any different for physical versus virtual environments. What am I not thinking about? Could you expand on this? Perhaps I've just not read the SQL 2014 licensing terms close enough.

bbrown

couberpu wrote:

Oh, Sorry about left out the error message.
I will get error message on tables that the company was not license for!

One example will be in sales order form, I will get read permission error on Campaign Target Group table which we were not licensed for.

Guess we were typing at the same time.

What security model are you using? (Never use Enhanced unless you have a specific reason)

couberpu

Did some digging & I realized that even I use Navision security sync function and no error was returned. IT DID NOT COMPLETE SYNC users.
I need to go through user list and make user mappings were correct. I should double check, but on the other hand, NAV should YELL at me for not be able to sync.

I do not want to go into too much detail. The issue is not SQL 2014 but 2008 R2 enterprise with 24 cores. Its like someone forgot to pay tax. :oops: :oops:
I got into this mess without any warming.

bbrown

I'd strongly suggest switching to Standard Security. Enhanced has been know to have several issues, and is generally not recommended. I stopped trying to use it years ago.

couberpu

Well, after matching users up and redid the sync. I am still getting same error message.
It still pointing to I am missing a user or something!
Should user dbo only show on the database or it should also show on master db?

Thanks

couberpu

Guess my problem is xp_ndo_enumusergroups.
Search for how to make it available in my database.

I have some windows logins too

couberpu

Still got same permission error message!!

Please help by point me to right direction.

bbrown

Sorry, but out of ideas at the moment. Your users should not require any more than membership in the PUBLIC roles at both the NAV database and SQL Server levels. You don't need any special SQL permission settings for regular NAV users.

As a test, you could create a clean NAV database and see if the same problem occurs. If it don't, then the issues is likely within the current database.

couberpu

Thanks.