SQL Database Security

The NAV Finder needs access to some of the tables in the SQL Database that Dynamics NAV users. It only needs read access to search for the information you are looking for. Unless you are a SQL Server administrator you need to set up the basic security.

Unfortunately it cannot reuse the permissions already assigned to users in Dynamics NAV. This meas that you have to setup the security specifically for the NAV Finder. It is a common procedure for the SQL administrator when assigning limited access to users and applications.

Security Models

There are different approaches you can take to implement the required security settings. I will try to describe some of them here. However, you do not have to use any of them if you can find a model that fits your database setup better. The goal is simply to give the NAV Finder permission to search the right tables.

#1 - Public Access

Maybe the simplest approach is to give the public database role the required permissions. That way all logged in users will inherit the permissions and you are done. The downside is that if the information is highly confidential then it may not be a good idea to give all logged in users access.

The following script will grant select permission to the tables used by the standard queries. Please note that you have to change the database and company name to match your installation.

-- Specify the database to use

USE [My Database]

 

-- Contacts and Customers

GRANT SELECT ON [dbo].[My Company$Contact] TO [public]

GRANT SELECT ON [dbo].[My Company$Customer] TO [public]

 

-- Sales Invoices

GRANT SELECT ON [dbo].[My Company$Sales Invoice Header] TO [public]

GRANT SELECT ON [dbo].[My Company$Sales Invoice Line] TO [public]

 

-- Sales Credit Memos

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Header] TO [public]

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Line] TO [public]

 

-- Purchase Invoices

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Header] TO [public]

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Line] TO [public]

 

-- Purchase Credit Memos

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Hdr_] TO [public]

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Line] TO [public]

 

#2 - Dedicated NAV Finder User

As an alternative to #1 you can create a dedicated database user for the NAV Finder. The trick is to create a new user and assign the correct permissions to that user. Then you can specify that user in the connection dialog in the NAV Finder. Please note that if your SQL Server does not permit database logins then you cannot use this method.

Here is a script that will illustrate that.

-- Specify the database to use

USE [My Database]

 

-- Create login if it does not exist

IF NOT EXISTS(SELECT loginname FROM master.dbo.syslogins WHERE loginname='nav_finder_login') CREATE LOGIN [nav_finder_login] WITH PASSWORD=N'pass@word1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

 

-- Create user if it does not exist

IF DATABASE_PRINCIPAL_ID('nav_finder_user') IS NULL CREATE USER [nav_finder_user] FOR LOGIN [nav_finder_login]

 

-- Contacts and Customers

GRANT SELECT ON [dbo].[My Company$Contact] TO [nav_finder_user]

GRANT SELECT ON [dbo].[My Company$Customer] TO [nav_finder_user]

 

-- Sales Invoices

GRANT SELECT ON [dbo].[My Company$Sales Invoice Header] TO [nav_finder_user]

GRANT SELECT ON [dbo].[My Company$Sales Invoice Line] TO [nav_finder_user]

 

-- Sales Credit Memos

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Header] TO [nav_finder_user]

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Line] TO [nav_finder_user]

 

-- Purchase Invoices

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Header] TO [nav_finder_user]

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Line] TO [nav_finder_user]

 

-- Purchase Credit Memos

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Hdr_] TO [nav_finder_user]

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Line] TO [nav_finder_user]

 

#3 - Create a NAV Finder Role

An approach where you create a database role for the NAV Finder users is my preferred model. This strategy works well with NAV users who logs into NAV using Windows Authentication.

The theory is simple. You create a database role and assign the table permissions to that role. Every time you want to allow a new user to use the NAV Finder you assign that user the NAV Finder database role.

A script for creating the role can look like this:

-- Specify the database to use

USE [My Database]

 

-- Create role if it does not exist

IF DATABASE_PRINCIPAL_ID('nav_finder_role') IS NULL CREATE ROLE [nav_finder_role]

 

-- Contacts and Customers

GRANT SELECT ON [dbo].[My Company$Contact] TO [nav_finder_role]

GRANT SELECT ON [dbo].[My Company$Customer] TO [nav_finder_role]

 

-- Sales Invoices

GRANT SELECT ON [dbo].[My Company$Sales Invoice Header] TO [nav_finder_role]

GRANT SELECT ON [dbo].[My Company$Sales Invoice Line] TO [nav_finder_role]

 

-- Sales Credit Memos

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Header] TO [nav_finder_role]

GRANT SELECT ON [dbo].[My Company$Sales Cr_Memo Line] TO [nav_finder_role]

 

-- Purchase Invoices

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Header] TO [nav_finder_role]

GRANT SELECT ON [dbo].[My Company$Purch_ Inv_ Line] TO [nav_finder_role]

 

-- Purchase Credit Memos

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Hdr_] TO [nav_finder_role]

GRANT SELECT ON [dbo].[My Company$Purch_ Cr_ Memo Line] TO [nav_finder_role]