SQL Security synchronize.

MrWho

Our Customer got 44 Companies in Navision, which means on SQL approximately 951 Tables (+SIFT tables etc) * 44 = 41844 Database tables in SQL. 18 Windowslogins and 4 Active Directory Groups. Multiplying 41844 Table with each login * 22 = 920568.

So I think it has to synchronize somewhat 920568 tables/times in total. This is only my own consideration of what’s going on behind all this, since it takes seriously shorter time on Companies only having one Nav Company. Because on this particular customer it takes 3 hours to synchronize, and then I’m running Navision client and synchronizing from the same server as the SQL Server is running on. Is there any best practices for these things, should I setup each company in it’s own SQL database, but then they will lose intercompany functionality and the close open company in one client, and I have to maintain security in each DB.

Please help, any angle to this matter would be appreciated.

DenSter

If you are on late enough exe's, set the security model to 'standard'. File, Database, Alter, Advanced tab, Security area). This will eliminate synchronizing permissions to the SQL Server. Those are managed from within NAV anyway. The only drawback is that this takes away specific table permissions on SQL Server, so users can't access the tables directly. In my opinion, the standard security model is 'safer' than the enhanced model, and it only takes a few seconds to synchronize.

nunomaia

You could use standard security model. It's faster. I think you should use all companies in same database, it's easier to maintain.

nunomaia

DenSter wrote:

If you are on late enough exe's, set the security model to 'standard'. File, Database, Alter, Advanced tab, Security area). This will eliminate synchronizing permissions to the SQL Server. Those are managed from within NAV anyway. The only drawback is that this takes away specific table permissions on SQL Server, so users can't access the tables directly. In my opinion, the standard security model is 'safer' than the enhanced model, and it only takes a few seconds to synchronize.

Ok, you were faster.

DenSter

this time I was

kriki

[Topic moved from Navision forum to SQL General forum]

ravi_navision

nunomaia wrote:

DenSter wrote:

If you are on late enough exe's, set the security model to 'standard'. File, Database, Alter, Advanced tab, Security area). This will eliminate synchronizing permissions to the SQL Server. Those are managed from within NAV anyway. The only drawback is that this takes away specific table permissions on SQL Server, so users can't access the tables directly. In my opinion, the standard security model is 'safer' than the enhanced model, and it only takes a few seconds to synchronize.

Ok, you were faster.

Hi,

My client also facing the same synchronize problem. (Nav 4.0SP3, SQL2005)
Before altering the security model on live database I want to hear the results from your side.

1. Do you face any problems after changing security model to standard
2. My test results: Before altering the database I was able to do synchronize single login. After altering synchronize single
login is in gray color
3. Can any body describe me more about “The only drawback is that this takes away specific table permissions on SQL Server, so users can't access the tables directly”


Thanks,
ravi

davmac1

Has anyone seen this error when synchronizing (NAV 4 SP2, SQL Server 2000):

Navision security synchronization error lock request time out period exceeded

This happened on a grant select to the General Ledger Setup table. The SQL error is 1222. I googled it and it appearsa the solution is to optimize the sql statements. The only way I know to do that is to go to standard security.

DenSter

ravi_navision wrote:

1. Do you face any problems after changing security model to standard

No problems

ravi_navision wrote:

2. My test results: Before altering the database I was able to do synchronize single login. After altering synchronize single login is in gray color

Because you don't need to synchronize anymore on the standard security model.

ravi_navision wrote:

3. Can any body describe me more about “The only drawback is that this takes away specific table permissions on SQL Server, so users can't access the tables directly”

With the enhanced security model, all table permissions are pushed into SQL Server, which is why sometimes it takes so long to synchronize. A user that has access to the NVA database through the NAV client could also connect to the database by other means. With the standard security model the permissions are all set up inside the NAV client, and individual users could not connect to the NAV database by other means, because no individual table permissions are set up in SQL Server. So if you have users that need suc access, and your NAV database is on standard, then you will have to manually grant any additional permissions. I would personally create separate users for those additional purposes.

ravi_navision

Thanks to all sharing your information

--ravi

frede

Yes - thanx a million - just saved me loads of time...Our test server took more than 5 hours synchronizing all users (SQL 2008 and NAV 5 SP1)! And that was with only 30 active users. Now I've just created another 50 users...

DenSter

Holy cow that is a bit excessive :shock: