Add Users in NAV 2016 from SQL Server

Add Users in NAV 2016 from SQL Server

Hi all,

    Today i came across with different type of requirement, Sometimes you might be got an error while opening the RTC. Error – ”You do not have permission to access .....”. See the bellow screenshot.

This error came, because user not exist in User Table, So if you are a first user then simply you can execute the SQL query, and you will resolve your issue.

The Sql Query is given bellow, just Open your Database and run this query.

USE [Demo Database NAV (9-1)]

GO

     DELETE FROM [dbo].[User]

     DELETE FROM [dbo].[Access Control]

     DELETE FROM [dbo].[User Property]

     DELETE FROM [dbo].[Page Data Personalization]

     DELETE FROM [dbo].[User Default Style Sheet]

     DELETE FROM [dbo].[User Metadata]

     DELETE FROM [dbo].[User Personalization]

GO

Now you are abale to open RTC.

Note: But whenever you have multiple users then you can’t execute this query because all users will be deleted, for that you have to do some more steps, that steps i’m going to describe.

Step 1:  Open Powershell ISE with Administrator privilege. Now execute this command

$objUser = New-Object System.Security.Principal.NTAccount("DOMAIN\BINESH.SINGH")

$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])

$strSID.Value

See the bellow screenshot for better understanding.

Once you will execute the above command, you will get the SID number. Save it off in Notepad OR somewhere. It will be used in the second step.

Step 2: Now open SQL Server and you need to execute the following Query for that Database in which you wants your respective login. SID and the Username copied from above. 

USE [Demo Database NAV (9-1)]

GO

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50), @USERSIDTXT varchar(50)

SET @USERNAME = 'DOMAIN\BINESH.SINGH'

SET @USERSID = NEWID()

SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)

SET @WINDOWSSID = 'S-1-5-21-611616715-3189593580-2499604960-1499'

INSERT INTO [dbo].[User]

 ([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],[Change Password],[License Type]

 ,[Authentication Email],[Contact Email])

 VALUES

 (@USERSID,@USERNAME,'Binesh Singh',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0,'','')

INSERT INTO [dbo].[User Property]

 ([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],

 [Authentication Object ID])

 VALUES

 (@USERSID,'','','','','1753-01-01 00:00:00.000','')

INSERT INTO [dbo].[Access Control]

 ([User Security ID],[Role ID],[Company Name],[Scope],[App ID])

 VALUES

 (@USERSID,'SUPER','',0,'{00000000-0000-0000-0000-000000000000}')

GO

See the bellow screenshot for Sql Query.

Execute the above command. If it gets successfully executed then you can go and Restart the NAV Server Instances and then try to login and you will get logged in to RTC.

How Do I Split Comments or Copy from somewhere and paste in comments.

How Do I Split Comments or Copy from somewhere and paste in comments.

Hi all,

    Today i came across with different type of requirement, where someone asked in community forum, How Do I Split Comments or Copy from somewhere and paste in comments.

This solution for Contact comments, you can use similar in your sales documents also, only you have to change the Record variable as per your requirement.

To achieve these requirement i created a Page of card type and two functions and two global variables.

Technology

    Microsoft Dynamics NAV 2017

Scenarios

   Demo 1 – Binesh creates a new page i.e. Name “Wrap Text” ID 50246.

   Demo 2 – Binesh calls the earlier created page in comments action.

Demo 1 – Binesh creates a new page i.e. Name “Wrap Text” ID 50246.

To achieve this follow the bellow steps:

Step 1:        Open Development environment and create a new page.

Step 2:       Create Global variables i.e.:

Name	DataType	Subtype	Length
CommentTxt	Text
RMCommentG	Record	Rlshp. Mgt. Comment Line

Step 3:       Design your page, see the bellow screenshot for better understanding.

               

              

                            

Step 4:       Create two functions in this page, see the bellow screenshot.

      

     

Step 5: In Function GetRMParam pass the record variable of "Rlshp. Mgt. Comment Line" as var true.
        And
In Function RMCommentEditor function declare local variables, See bellow screenshot.

Step 6:       Write the code, see the bellow screenshot

Step 7 :      Now call the function in OnQueryClosePage Action of this page,

See the bellow screenshot.

  

Step 8:       Now Open the Rlshp. Mgt. Comment Sheet  Page, After that open Page Action, and then create an Action i.e. Wrap Comment, See the bellow screenshot.

Step 9:       Go to C/Al Editor of Wrap Comment Action, and declare a local variable of page type that are earlier created, and pass the record parameters and then Run the page, See the bellow screenshot for better understanding.

Step 10: Testing solution -> Open Windows/Web client -> Open Contact List page -> select any contact -> click on Comments Action - > you will get Comment page -> In comment page if you have more comments, or you want to use copy, paste then click on Wrap Comment Action -> A Comment Editor page will open, In that editor you can paste your comments (or) type your comments -> After that click OK or Close button -> Comments updated in comment page.
For better understanding see thee bellow screenshot.

Reporting data setup tool in Microsoft Dynamics NAV 2017

Reporting data setup tool in Microsoft Dynamics NAV 2017

Hi All,

    Today, I came with amazing article in NAV 2017 i.e. "Reporting data setup tool in NAV 2017".

First of all, I would like to say thanks to NAV developers to add the amazing data setup tool.

Technology

    Microsoft Dynamics NAV 2017

Demo preset configuration

    This script was written to support Microsoft Dynamics NAV 2017. To run this demonstration, ensure that a demo site has been created, is up and running and that you have logged on. 

Login information: 

• Domain: The Nail Superstore

• User: Binesh

• Password: Pass@word1

Demo instructions

    The demonstration scenarios assume that you have created a new company using the ‘The Nail Superstore’ functionality using the Dynamics NAV 2017 settings. This functionality will not be available in the Cronus sample companies.

The demonstrations scenarios build on each other.

Demo data preparation

    For demo purpose use Business Manager.  This will be the default role when the site is provisioned.

Scenarios

   Demo 1 – Binesh creates a new reporting data set 

   Binesh chooses to create a new data set from the customer list page data

   Demo 2 – Binesh edits an existing reporting data set

   Binesh chooses to edit an existing data set that he was created in order to make a simple change [This Demo i update later].

   Demo 3 – Binesh creates a new reporting data set from a copy of an existing data set 

   Binesh chooses to create a new data set based on a copy of an existing data set [This Demo i update later].

Demo 1 – Binesh creates a new reporting data set

Demo story: Binesh want to view refreshable customer data in Excel

To achieve this follow the bellow steps:

Step 1: Open Dynamics NAV 2017.

Step 2: In search box in the upper right side, enter 'Assisted Setup' and select the related link [See the bellow screenshot].

Step 3: Select the ‘Setup Reporting Data’ option from Assisted Setup [See the bellow screenshot].

Step 4: Select ‘Next’ after the welcome page [See the bellow screenshot].

Step 5: From the drop down, select ’Create a new data set’ and select ’Next’ [See the bellow screenshot].

Step 6: Enter a name for your data set ‘Customers’ [See the bellow screenshot].

Step 7: Select a data source ID.  The Data Source Name will automatically appear once the ID is selected. ID = 22 [See the bellow screenshot].

Step 8: Make sure a data source type; ’Page’ [See the Upper screenshot].

Step 9: Select 4 fields that you want to include in your data set
 No., Name, Country_Region_Code, Phone_No, Contact, Balance_LCY, Balance_Due_LCY, Sales_LCY

 [See the bellow screenshot]. And Select the ’Add Filters’ button

Step 10: In the ’Edit-Filter Page’, add a filter to one of the fields you selected and click ’OK’ [See the bellow screenshot].

Step 11: Select ’Publish’ [See the bellow screenshot].

Step 12: Select the ‘Open in Excel’ button. [See the bellow screenshot].

Step 13: Once you clicked on Excel Button,  spreadsheet downloading is start. [See the bellow screenshot].

Step 14: The fields and data, based in filter applied, should appear in the spreadsheet.

Step 15: Once you tested your data or download is complete then click on finish button. [See the bellow screenshot].

Conclusion
This set of demonstrations has presented the Reporting Data Setup feature for Microsoft Dynamics NAV 2017, which helps business people better analyze the financial performance of their business. The Reporting Data Setup feature provides the following benefits:
• Create an OData end point for any page or query in Dynamics NAV 2017
• Leverage the OData end points in Power BI, Excel, PowerApps, and Microsoft Flow
• Existing Web Services security is used

References:

               mbspartner source [download ppt]
Note:   Some content is copied from mbspartner source.