NAV 2016 - You do not have access to Microsoft Dynamics NAV - Create User From SQL stored procedure

Where we try to open the NAV2016 database,we might get the below error from Role Tailored Client -

You do not have access to Microsoft Dynamics NAV. Verify that you have been set up as a valid user in Microsoft Dynamics NAV







Copy the below stored procedure


USE [DATABASE NAME]
GO
/****** Object:  StoredProcedure [dbo].[createNAV2015User]    Script Date: 18-12-2015 13:54:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Description:     Add a user & login to SQL and add this to NAV
-- =============================================
CREATE PROCEDURE [dbo].[createNAV2016User]
       -- Add the parameters for the stored procedure here
       @Username varchar(200)
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
      
       DECLARE @BinSID AS VARBINARY(100)
       DECLARE @StringSID VARCHAR(100)
       DECLARE @i AS INT
       DECLARE @j AS INT  
       DECLARE @SQLString As Varchar(250)     
                  
       SELECT @BinSID = SUSER_SID (@Username,0)
   
       IF LEN(@BinSID) % 4 <> 0
       begin
             PRINT 'No valid SID found, exiting code'
             RETURN(NULL)
       end
      
       SELECT @StringSID = 'S-'
          + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
       SELECT @StringSID = @StringSID + '-'
          + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))


       SET @j = 9
       SET @i = LEN(@BinSID)
       WHILE @j < @i
       BEGIN
         DECLARE @val BINARY(4)
         SELECT @val = SUBSTRING(@BinSID, @j, 4)
         SELECT @StringSID = @StringSID + '-'
             + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
         SET @j = @j + 4
       END   
      
       BEGIN TRY
    DECLARE @GUID nvarchar(50)
    SET @GUID = NEWID()
    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
    (@GUID, @Username, '', 0, N'1753-01-01', @StringSID, 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
    (@GUID, '', '', '', '', N'1753-01-01', '')
    INSERT INTO [dbo].[Access Control]
    ([User Security ID], [Role ID], [Company Name], [App ID], [Scope])
    VALUES
    (@GUID, 'SUPER', '', '00000000-0000-0000-0000-000000000000', 0)
             IF EXISTS(SELECT TOP 1 1 [User Security ID] FROM [Access Control] WHERE [User Security ID] = @GUID)
                    PRINT 'User added to Windows logins in Dynamics NAV as SUPER'            
       END TRY
       BEGIN CATCH
             PRINT ERROR_MESSAGE();
             RETURN(NULL);      
       END CATCH
END

GO


and  open the SQL Server Management Studio(SSMS) and right click on the data base--> New Query and paste the copied stored procedure





change the database name and execute the stored procedure






then you can find the stored procedure in the below path





and right click on the stored procedure and execute and give your user id. User id will be created.

Released Cumulative Updates for Microsoft Dynamics NAV 2017

A cumulative update is a cumulative set of files that includes all hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2017

Cumulative Updates for Microsoft Dynamics NAV 2017

Knowledge Base ID	Title	Release date	Build no.	Local versions included
4024642	Cumulative Update 07 for Microsoft Dynamics NAV 2017	June, 2017	16996	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
4021396	Cumulative Update 06 for Microsoft Dynamics NAV 2017	May, 2017	16585	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
4018437	Cumulative Update 05 for Microsoft Dynamics NAV 2017	April, 2017	16177	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
4014101	Cumulative Update 04 for Microsoft Dynamics NAV 2017	March, 2017	15601	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
4011763	Cumulative Update 03 for Microsoft Dynamics NAV 2017	February, 2017	15140	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
3216190	Cumulative update 2 for Microsoft Dynamics NAV 2017	January, 2017	15052	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK
3209071	Cumulative update 1 for Microsoft Dynamics NAV 2017	December, 2016	14199	AT, AU, BE, CH, CZ, DE, DK, ES, FI, FR, IS, IT, IN, NA, NL, NO, NZ, RU, SE, UK

Obsolete tables in NAV2016

The below list of tables were obsoleted\removed in NAV2016.

  Tables  452,453,464,465 and 11414

Internal error: Could not load file or assembly 'Microsoft.Dynamics.Nav.DotNetBridge, Version=8.0.0.0

Some time we may get the below error while import the objects

---------------------------
Microsoft Dynamics NAV Development Environment
---------------------------
Internal error: Could not load file or assembly 'Microsoft.Dynamics.Nav.DotNetBridge, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
---------------------------
OK
--------------------------- Solution. This is due to  running the client from C\program files (x86) folder which is for 32 bit applications . We should run the dev client from C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client to resolve the issue.

Error : There is a syntax error while compiling NAV16 object

Recently I got an issue with compiling the object in NAV2016, I am getting the below error while compile the object .

---------------------------
Microsoft Dynamics NAV Development Environment
---------------------------
There is a syntax error.
---------------------------
OK
---------------------------

But when I check the code everything seems to correct as show below

Now I copied the code in notepad++ and found some special characters as  shown below 

   

I removed them and used the code in NAV and my object got compiled. I hope it will help some one.                                                                                                                   

Login failed for user 'XXXX'. 4060,"42000",[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database XXXXXX requested by the login. The login failed.

The below error occurs when we try to convert the database from NAV2009 to NAV2013.


The following SQL Server error or errors occurred:

18456,"28000",[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'XXXXXX' 4060,"42000",[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "XXXXXX'" requested by the login. The login failed.

Solution:

In this case ,please follow the below steps

1. We have to restore the NAV2009 backup again 

2. Open the database In NAV2009 and remove all tables which have property “LinkedObject“ Yes 

Note: Please also check the tables which are having LinkedObject=No.

It should be like below 

3. Try to convert the database in NAV20013.
4. Delete all temp windows logins\SID's

Now It will convert it in to NAV2013.

I hope this will help you.

Thanks!