Minimium Permissions for User sync - Nav 9.0 and SQL 2005 SP3

Hi All,

We have reccently installed Nav 9.0 still on SQL 2005 SP3. I would like to give our business appplication support area the ability to manager user rights\roles for our Nav databases. I have given the relevant users the following access(as per the Nav 9.0 installation guide)

  1. Fixed server roles of SecurityAdmin

  2. DB_owner of the relevant Navision DB

Although I have given these permissions, when the users try to sync they get the following error:

You do not have the required SQL server permssions to perfrom the curent security activity

The only way I have got around this was to grant the relevant users db_securityadmin on the master database.

Has anyone else experienced this? My only issues is does this grant the user additional right I should be aware of since the database server is home for a number of our Enterprise applications.

Thanks. Michael.

Are you using Enhanced Security? If so Why?

Nope. Using Standard security.

I have actually also noticed, that although the user has these rights they are unable to add any new users and also get the error.

You do not have the required SQL server permssions to perfrom the curent security activity

Does anyone have a work around for this with our having to give a user SA rights on the SQL server?

Thanks

Hi!

Users (or Admins) who should be able to manage the Logins & Permissions need to be assigned to those roles:

SQL Server: securityadmin
Database: db_securityadmin and db_accessadmin

Does this work?

Cheers,
Jörg

Hi Jörg

Well I have given the user securityadmin at the server level and at the database level DBowner and it still did not work.

One thing I noticed is that if I gave the user db_securityadmin on the master database it allowed the relevant user to sync, but they are unable to add new users(they recevied the same error)

It would be good to know if anyone has been able to successful have a user perform full sync and ability to add/remove users without having Sysadmin permissions on the SQL server?

Thanks

Again: it’s the db_accessadmin that does the trick …

Yep. I had given both DBowner and db_accessadmin of the Navision DB in question and still get the same error.

In fact I have noticed the error I get when I try to add a new users under such a user account is:

The following SQL error or error occured:

15151,“42000”[Microsoft][ODBC][ODBC SQL Server Driver][SQL Server] Cannot find the object ‘sysprocesses’, because it does not exist or you do not have permissions

SQL:

GRANT SELECT ON sysprocesses to [Domain\Username]

The domain\username is actually the users I’m trying to add(i haven’t included this).

Jorg, Do you have a test environment that you test a similar setup?

Well, it’s really hard to tell what going wrong there … normally the roles mentioned above should be sufficient enough.
Maybe the users are inheriting other rights - denials - from other roles?

try to grant the admin user grant option on sysprocesses.

something like

grant select on sysprocesses to [domain/admin] with grant option

Hi Daniel,

Thanks for the post. I have been meaning to post the relevant permissions I had to set to get around the sync issue which does include the granting of rights on the sysprocesses view in the Master database. For anyone else having similiar issues these are the permissions I needed to set to allow a users the rights to do all relevant user managements tasks(e.g. Addition/deletion of users/groups/roles, user sync, object modifications) without requiring to give Sys Admin fixed server role(due to the SQL server hosting additional enterprise application databases)

  • Give SecurityAdmin Fixed server role
  • Give user DB owner rights database role on Navision DB
  • Give user DB_accessadmin database role on Master database
  • Give user Grant on Select permissions for sys.sysprocesses

Hope this helps anyone else who has had similiar issue.

Michael