SQL Server Role for NAV Users

ta5
ta5 Member Posts: 1,164
edited 2012-01-18 in SQL General
Hello

Following up to this:
viewtopic.php?f=23&t=47512

I know, normal users don't need special roles on sql server. My question is:
What server role is needed for managing these tasks from within NAV 2009R2 on SQL Server 2008R2:

1: Creating and deleting Windows Logins
Imho user must have sysadmin. I wonder why securityadmin does not work.

2: Creating and deleting DB Logins
Imho user must have sysadmin. I wonder why securityadmin does not work.

3: Import FOBs that contain changes to tables (alter table, create table, delete table)
Imho user must have database owner (no server role, but database role).

4. Import license
Imho it depends on whether the license is set to be stored in nav db or in master db.

I'm looking for some kind of best practices.

Thanks in advance
Thomas

Answers

  • rhpnt
    rhpnt Member Posts: 688
    Maybe this will help...
  • ta5
    ta5 Member Posts: 1,164
    Thanks. This helps a little, but does not answer the questions mapped to the tasks. Do you have more info?
    Thanks in advance
    Thomas
  • rhpnt
    rhpnt Member Posts: 688
    ta5 wrote:
    I'm looking for some kind of best practices.
    As far as I know there aren't any. Basically you've answered it yourself: the "simple" user needs no special role because he doesn't alter any SQL server related objects directly. The only role that covers the needs of a user which imports objects, adds logins/users... hence works with a developer license is "sysadmin".
  • ta5
    ta5 Member Posts: 1,164
    Thanks!
    That was basically what I expected. Does anybody experience something else?
    Thanks in advance
    Thomas
  • Capone
    Capone Member Posts: 125
    edited 2012-01-18
    I found this in MS documentation that I think came with the cd.

    Synchronization
    For a user to be able to invoke the synchronization process or modifying the User table it requires membership of following Server or Database Roles:
    Sysadmin server role. Alternatively both a member of the securityadmin server role and a member of the db_owner database role for this database

    Altering a Database
    Altering a database demands sysadmin or dbcreator server role. Alternatively a member of the db_owner or db_ddladmin database role for this database.

    Delete Database sessions
    Sometimes the database can run out of user licenses which means that no new users can connect to the database. To get around this problem you can throw out users that have idle or double sessions (one session/client).
    The Database Sessions window lists details of all the sessions that are currently connected to the database and the time when each user logged on.
    The administrator must be a member of either the sysadmin or processadmin SQL Server server rolls to be able to delete sessions


    When it comes to sysadmin I think that most two people in this organisation should have this role since this gives all access to the entire server.

    Edit: Forgot to copy paste required roles to delete database sessions
    Hello IT, have you tried to turn it off and on?
    Have you checked the cables?
    Have you released the filters?
    http://www.navfreak.com
  • ta5
    ta5 Member Posts: 1,164
    Hi Capone
    Thx a lot for your input on this.
    Regards
    Thomas

Categories