Impact of User Roles in SQL in NAVISION.

Hi all,

I was thinking about this post form a long time. It's about roles assigned to user in SQL.

Let's see how these roles effect us in Navision.



1) I will create a SQL Server Authentication (Database Login) in SQL Server for testing as shown below.

2) I just provided the username and password. By Default SQL Server Provides Public Role to User.

3) I will add this user in navision database with super role and syncronize it.

4) PUBLIC can do -
   a) Add fields in Form if it exist in table.
   b) Add Code in Objects.
   C) View Database Permission.

   PUBLIC Cannot do -
   a) Add fields in tables.
   b) Alter Database.
   c) Create Database.
   d) Delete Database.

5) Let's provide this user role of DB owner for a database.
 * Remember - DB Owner is for a database not for SQL Server, you will find that under database security.

6) DB Owner can do - 
   a) Add fields in Form if it exist in table.
   b) Add Code in Objects.
   C) View Database Permission.
   d) Add fields in tables.
   e) Alter Database.
   f) Create Database.
   g) Delete Database.

6) Let's provide this user role of Sys Admin for a database.
 * Remember - Sys Admin is for SQL Server Level Role, you will find that under SQL security.

7) Sys Admin Owner have same rights as of DB Owner but DB Owner is specific to database and Sys Admin is for all the databases.

So above is the list of conman roles we provide to users in SQL for NAV but Lets see description of all roles as per Microsoft.

Server Level Roles -

bulkadmin  - Members of the bulkadmin fixed server role can run the BULK INSERT statement.

dbcreator  - Members of the dbcreator fixed server role can create databases, and can alter and restore their own databases.

diskadmin  - The diskadmin fixed server role is used for managing disk files.

processadmin  - Members of the processadmin fixed server role can terminate processes that are running in an instance of SQL Server.

securityadmin  - Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

serveradmin  - Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

setupadmin  - Members of the setupadmin fixed server role can add and remove linked servers, and also execute some system stored procedures.

sysadmin - Members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, are members of the sysadmin fixed server role.

Database Roles -

db_owner - Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

db_securityadmin - Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.

db_accessadmin - Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator - Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin - Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter - Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader - Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter - Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader - Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

I hope You will find this post useful.

Regards,
Saurav Dhyani
http://saurav-nav.blogspot.in/