SQL Permissions for Administrators

dsearle

Hi,

We are running NAV 2009 SP1 on SQL 2005 in Standard Security Mode and are trying to get to grips with the required SQL permissions for our Administrators.

The functions our Admins will be performing:
Adding/Removing/Amending Windows Logins
Importing/Exporting Objects

We have given all of the admins the SUPER role in NAV, however when it comes to SQL things are a little tricky...

Our DBA team have requested that we investigate the minimum SQL permissions required to allow our admins to perform their functions. We have done some testing and at the very minimum we have discovered that each admin needs to have:

SQL Server Roles
Security Admin
Public

Database Roles - MASTER
DB Owner
DB Security Admin

Database Roles - NAV
DB Owner
DB Security Admin

This still concerns our DBA team as they are reluctant to allow DB Owner rights on the master/nav databases if avoidable so we have been asked to do some more research.

Can anyone help me with this, is there a way of avoiding allocating the DB Owner role to our administrators? What is the minimum level of permissions required to allow our admins to do their jobs?

Thanks for taking the time to read, I look forward to hearing back from you.

Cheers,
Dave

Tvis

Interesting subject. I enjoyed reading this blog post here written by Jörg Stryk:
http://dynamicsuser.net/blogs/stryk/arc ... v-sql.aspx

Makes you wonder about security in more ways than just: 'who gets to be db owner'

stryk

Hi!
The "Installation & System Management (SQL)" PDF describes which roles are required for certain purposes:

Creating Databases:

sysadmin or dbcreator server role. Alternatively, the user must have
been granted the create database permission. The user must also
have public access to the model database.

Altering DB:

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

Creating tables:

sysadmin server role or be a member of the db_owner database role
for this database.

Changing tables:

In order for a user to create or modify table definitions in Dynamics NAV, they must be
a member of the db_owner database role (the database creator is automatically a
member of this role).

Changing NAV Security Model:

To change the security model used in the database, you must be:
• A member of the sysadmin server role on SQL Server or be a member of the
db_owner database role for the database in question.
• Assigned the SUPER role in Dynamics NAV.

User Setup:

sysadmin server role. Alternatively both a member of the
securityadmin server role and a member of the db_owner database
role for this database

Just check out the PDF!

Cheers,
Jörg

detlef

Hidden in nav_install.chm:

Users
Add and synchronize users
db_accessadmin for the master database; db_accessadmin and db_owner for Microsoft Dynamics NAV databases

In addition, you must provide additional privileges by following these steps:

Open SQL Server Management Studio and connect to your SQL Server instance.

On the File menu, point to New, and then click Query with Current Connection.

Type the following SQL statements.

CopyCode imageCopy Code
GRANT SELECT ON sysprocesses WITH GRANT OPTION TO [username]
GRANT VIEW SERVER STATE WITH GRANT OPTION TO [username]
GO


Highlight the lines that you just typed and, on the Query menu, click Execute.

afarr

detlef - Thanks for mentioning those SQL roles and commands.
The commands gave a syntax error, but the syntax below worked well (on SQL 2008 SP3):

GRANT SELECT ON sysprocesses TO [username] WITH GRANT OPTION
GRANT VIEW SERVER STATE TO [username] WITH GRANT OPTION
GO