Make NAV User – SQL + PowerShell

Hey Friends,

It’s a very simple Post on how to make a NAV User from SQL/Powershell. Many times we end up with the following Error.

0

Though if you are first user login in then you can follow this POST to quickly solve it out, but if there are many valid users then obviously not.

So the first step that need to be done is to open Powershell ISE with Administrator privilege. Now execute this command

capture

//RWN
$objUser = New-Object System.Security.Principal.NTAccount(“DOMAIN\USER”)
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value
//RWN

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.

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

capture

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

SET @USERNAME = ‘DOMAIN\USER
SET @USERSID = NEWID()
SET @USERSIDTXT = CONVERT(VARCHAR(50), @USERSID)
SET @WINDOWSSID = ‘YOUR COPIED SID

INSERT INTO [dbo].[User]
([User Security ID],[User Name],[Full Name],[State],[Expiry Date],[Windows Security ID],[Change Password],[License Type]
,[Authentication Email])
VALUES
(@USERSID,@USERNAME,”,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])
VALUES
(@USERSID,’SUPER’,”)
GO
–RWN

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

Advertisements

SQL VIEW – NAV

 

Hey Friends,

I just experienced a case where Client want to see a common list Page in NAV where he can see a data which is basically a combination of multiple Tables. Though it’s achievable in NAV but you need to write a bulky code on the page which eventually turns off the Performance.

So what I did I made a SQL view for this combination which is very simple from SQL’S point of view.

untitled

When you will execute the above view in SQL you will have the exact look and feel what client was expecting in NAV. So what you need to do is you have to make a new Table with the same name as of the view and with the same columns as shown below

untitled1

Now you have to turn on the below property of the Table to get this interconnected.

untitled3

Now when you will run this Table you will get the same desired results of  what you experienced in SQL.

P.S – Only do this when you have a combination of multiple tables and is really critical, as it’s consuming a Table Object in NAV.

Create SQL Job – Stored Procedure

 

Hey Friends,

Sometimes a situation comes were NAV Job stops and we keep on debugging but yet unable to stump the real cause. Even after continuously tracing in Event Viewer we dint found any solid reason for this. So to solve my scenario I decided to write a SP(Stored Procedure) which eventually I attached it as a SQL Job which is supposed to be one of the best scheduler in Programming World.

So here I am to just share with all you as How to Make a SQL Job. 

Login to your SQL Management Studio. In Object explorer you will find SQL SERVER AGENT. Make sure you have a level of Permission in SQL otherwise you wont be able to see this. Right Click on Job and make a new Job.

1

Now you will be able to see a separate Page – Here you have to fill some Information as Name of your JOB, Owner(By Default it will be yours) etc. – Make it enabled as shown in the below Screenshot.

Capture

Now you have to click on Steps – Here you need to fill some mandatory information as Database Name, Command – Procedure Name.

This EXEC command is going to execute your Procedure on regular Intervals.

Capture1

You may click on Advanced on the left Panel and make your Job some more conditional based as what you want to do on Success or on failure of the Job.

Capture2

Now you need to navigate to Schedule menu(Second Screenshot). This is the place where you need to define what will be the recurring timing your Job OR at what intervals or conditions you need to execute your Job.

Capture3

Now you may navigate to Alert menu if you want to make your Job a level more customized. Here you can define Job to raise an alert if the message contains some specific value. If not needed you can skip this. I dint mentioned this in my Job.

Capture4

Now there is a separate menu for notification which you can use use for mail transmissions if Job fails with additional more services.

Capture5

Now simply press OK. Open the first Screenshot page and press View History. Here you can see the logs of your running Job.

CHEERS.

NAV 2013 – Open as a Fresh User with Default Settings.

 

Sometimes doing an operation from SQL side is much easier then exceuting it from NAV itself. If you want to use NAV as a fresh user with all default settings then you just need to execute a dell or truncate SQL command for these Tables

 USE [DBName]; 
 delete FROM [Access Control];  
 delete FROM [User];  
 delete FROM [User Property];  
 delete from [User Personalization];  
 delete from [User Metadata];  

              OR
 USE [DBName]; 
 Truncate Table [Access Control]; 
 Truncate Table [User]; 
 Truncate Table [User Property]; 
 Truncate Table [User Personalization]; 
 Truncate Table [User Metadata]; 

 Enjoy.

xp_ndo.dll Error – Extended Stored Procedure

 

Hello All,

Below Screenshot is a very common issue which we often face if anyhow there’s a change in the bit of the Operating System.

Error-

The following SQL server error or errors occurred:
17750,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL C:Program Files (x86)Microsoft Dynamics NAV60Databasexp_ndo.dll, or one of the DLLs it references. Reason: 193(failed to retrieve text for this error. Reason: 15100).
SQL:
INSERT INTO [#$ndo$groups] {CALL [master]..[xp_ndo_enumusergroups](?,?)}
 
Reason – Installed xp_ndo.dll is 32 bit and not 64 bit.
Simply replace xp_ndo.dll with the 64 bit version.
 
How to Do –
  • Stop SQL server.
  • Copy xp_ndo_x64.dll from installation DVD  to C:Program Files (x86)Microsoft Dynamics NAV60Database.
  • Rename existing xp_ndo.dll to xp_ndo_x32.dll.
  • Rename xp_ndo_x64.dll to xp_ndo.dll.
  • Start SQL server.
 Enjoy.