Wednesday, 12 March 2008

Dynamics NAV SQL Security Roles

I'm studying for the Installation and Configuration exam at the moment and have found some interesting stuff in the training material that I often need to know. I was recently asked about the SQL permissions needed to be able to create new users in Dynamics NAV and, thanks to my studies, was able to go straight to the right page in the document. I was interested to see that this topic also recently surfaced again in the DynamicsUser forum. This table is for Dynamics NAV 5.0.

Invoking the synchronization process or modifying the User table

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

Creating a database

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 a database

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

Creating tables within a database

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

Now you could argue that you don't want to grant these SQL rights to the user just to let them add users to the ERP database. This raises an interesting question: which is better, giving a user db_owner role membership and securityadmin server role or knowing that any user of the application could potentially add other users to the database? Personally I like the thought that I can pick and chose which user gets these rights from a SQL administrator's role and not leave it up to the users of the ERP to decide.


Ian said...

Good luck with the Install and Config. exam.

I remember it being quite tricky, let us know how you get on.

Gaspode said...

Thanks Ian. I'll make a post if I pass. If I fail then I'll pretend it never happened, delete this posting and your comments. ;-)