Tuesday, September 9, 2014

Create the windows login in NAV using SQL Query

Hi,

Use the following query to create the windows user in NAV Database using sql query.
Open the SQL Management Studio and run the following query on your relevant database.

D365BC 22 - OnPrime

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'intech-systems\navadmin'                           /*Domain Name\windowsid*/
SET @USERSID    =  NEWID();
SET @WINDOWSSID = 'S-1-5-21-755842481-2054525155-1331847812-1213'   /* USER SID*/


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],[Exchange Identifier],[Application ID])
    VALUES
          (@USERSID,@USERNAME,'',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0,'','','','00000000-0000-0000-0000-000000000000')

INSERT INTO [dbo].[User Property]
          ([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],[Authentication Object ID],[Directory Role ID],[Telemetry User ID])
    VALUES
          (@USERSID,'','','','','1753-01-01 00:00:00.000','','','00000000-0000-0000-0000-000000000000')

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

 ==================


D365BC
DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'INTECHSYSTEMS\NILESHG'                           /*Domain Name\windowsid*/
SET @USERSID    =  NEWID();
SET @WINDOWSSID = 'S-1-5-21-3002391015-913717614-2845076344-1997'   /* USER SID*/


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],[Exchange Identifier],[Application ID])
    VALUES
          (@USERSID,@USERNAME,'',0,'1753-01-01 00:00:00.000',@WINDOWSSID,0,0,'','','','00000000-0000-0000-0000-000000000000')

INSERT INTO [dbo].[User Property]
          ([User Security ID],[Password],[Name Identifier],[Authentication Key],[WebServices Key],[WebServices Key Expiry Date],[Authentication Object ID],[Directory Role 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

 ==================
NAV 2016

 DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'DOMAINNAME\NILESHG'                           /*Domain Name\windowsid*/
SET @USERSID    =  NEWID();
SET @WINDOWSSID = 'S-1-5-21-3002391015-913717614-2845076***-****'   /* USER SID*/


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,'',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

====================

NAV 2015

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'DOMAIN\NILESHG'    /*Domain Name\windowsid*/
SET @USERSID    =  NEWID();
SET @WINDOWSSID = 'S-1-5-21-3002391015-913717614-2845076344-1997'   /* USER 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,'nileshg@domain.com')

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

----------------------------------------------------

NAV 2013 R2

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'DOMAIN\NILESHG'    /*Domain Name\windowsid*/
SET @USERSID    =  NEWID();
SET @WINDOWSSID = 'S-1-5-21-3002391015-913717614-2845076344-1997'   /* USER 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,'nileshg@domain.com')

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

----------------------------------------------------

NAV 2013

DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = 'DOMAIN\NILESHG'      /*Domain Name\windowsid*/
SET @USERSID    = NEWID();
SET @WINDOWSSID = 'S-1-5-21-3002391015-913717614-2845076344-1997'    /* User SID */
INSERT INTO [dbo].[User]
          ([User Security ID],[User Name],[Full Name],[State],[Expiry Date],
           [Windows Security ID],[Change Password],[License Type])
    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])
    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

 ----------------------------------------------------

NAV2009/NAV 2009 R2

INSERT INTO [dbo].[Windows Login]
           ([SID])
     VALUES
           ('S-1-5-21-3002391015-913717614-2845076344-1997') *Domain Name\windowsid*/
GO
INSERT INTO [dbo].[Windows Access Control]
           ([Login SID]
           ,[Role ID]
           ,[Company Name])
     VALUES
           ('S-1-5-21-3002391015-913717614-2845076344-1997',
            'SUPER',
            '')
GO

 ----------------------------------------------------

Get the SID of any user in domain by following command write in cmd

wmic useraccount get name,sid




















OR

Only get current login user name and SID detail by following command

whoami /user








Posted by at
Labels:

1 comment:

  1. AnonymousJanuary 9, 2015 at 4:27 PM

    Thank you so much it is very useful query....

    ReplyDelete

Subscribe to: Post Comments (Atom)