Nav 2013 - How to create user without accessing RTC

sunmorningindia · 2012-11-06

I have done a Nav 2013 upgrade for one of the customer. Everything is working fine on my machine. Now, I take the backup and restore the same on client environments, changed the CustomSettings.config file so that new database can be accessed from RTC. Now system is giving error regarding User "Not valid Microsoft Dynamics User". Can anyone help me to create the user without having access to RTC client

Chinmoy · 2012-11-06

The Windows user through which you are logging into the system needs to be added to the Windows Logins inside the NAV database.

Jatin_Patel · 2012-11-06

Add user from SQL Server Management Studio and give proper rights to it.

sunmorningindia · 2012-11-06

Can you please tell me how to do this in Nav 2013?

sunmorningindia · 2012-11-06

Jatin, user is already added in SQL and given DB owner permission for this database

kine · 2012-11-06

If there is some problems with users, there is one thing: if you remove all users from the database through SQL (records from the NAV table), the first user who will connect to the database (NST) will be added and assigned SUPER permission set.

sunmorningindia · 2012-11-07

We tried some tricks (delete record from user table), but it’s not working.
The repro steps are:
1. Take NAV 2013 DB, here user are configured like: domainA\userA. Apply customization and data migration as applicable.
2. Take CSIDE backup
3. Restore in different domain environment like domainB\userB
4. Here SQL & NAV service are running under Network Service account.
5. Now we are unable to open RTC, in CSIDE there is no option to create user.

Please suggest any workaround here.

kine · 2012-11-07

Form the NAV 2013 online help, chapter "Restoring a Database in a Different Domain":

You may need to restore a Microsoft Dynamics NAV database in a situation where the users you had previously created are no longer available. For example, if you have restored your database in a different Windows domain or on a different network altogether. In this situation, you must take extra steps to assure that you will be able to run the Microsoft Dynamics NAV Windows client and connect to the database.

One option is to truncate the four tables that contain user data. When there are no users in the database, the first user who runs Microsoft Dynamics NAV Windows client is automatically added to the database and granted the SUPER permission set. For more information, see First User Is Automatically Assigned the SUPER Permission Set.

To truncate the tables, run SQL Server Management Studio and execute a query like the following:

Copy Code
USE <restored_db>;
TRUNCATE TABLE "User Personalization";
TRUNCATE TABLE "User Property";
TRUNCATE TABLE "Access Control";
TRUNCATE TABLE "User";
GO

The disadvantage of this option is that you lose all your user data, and must therefore recreate all users.

A second option is to use SQL Server Management Studio to update the User table in the Microsoft Dynamics NAV database to update Windows Security IDs for the new domain. For each user, run a query like the following:

Copy Code
UPDATE User SET [Windows Security ID] = '<new_Windows_security_id>' where [user name] = '<username>';
GO

mdPartnerNL · 2012-11-07

Excellent info. Do you have a link to this? may be already have it

but have too much on my desk.

kine · 2012-11-07

First sentence in the post: Form the NAV 2013 online help, chapter "Restoring a Database in a Different Domain":

mdPartnerNL · 2012-11-07

http://msdn.microsoft.com/en-us/library ... 5(v=nav.70).aspx

found it.

cr4cks · 2013-03-06

The second think does not work...

Copy Code
UPDATE User SET [Windows Security ID] = '<new_Windows_security_id>' where [user name] = '<username>';
GO

Also i had try to change the Username to NewDomain\Username, but nothing. It's dont work...

Who have any idea?

I have found THE solution... ;-)

T-SQL Statement:

USE [Demo Database NAV (7-0)]
DECLARE @USERSID uniqueidentifier, @WINDOWSSID nvarchar(119), @USERNAME nvarchar(50)
SET @USERNAME   = '<domain\user>'
SET @USERSID    = '<random security id>'
SET @WINDOWSSID = '<windows security id>'

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

It's work fine!

Sorry for my bad english... ;-)

paurola · 2013-03-11

How do you get the existing sids from SQL Server or AD to be used in:
SET @WINDOWSSID = '<windows security id>'

...and in the right format?

/Pauli

paurola · 2013-03-12

I will answer myself.

Create a SQL Server funtion that converts the SID to String format, you can find an example here http://www.sqlservercentral.com/scripts/SID/62274/

Then use this function in cr4cks T-SQL script:
SET @USERNAME = 'MyDomain\MyName'
SELECT @WINDOWSSID = dbo.fn_SIDToString(sid) from dbo.syslogins WHERE name = @USERNAME

That should do it.

rsaritzky · 2013-11-08

I ran across a command-prompt command that will list all the user SID's:

Open a command prompt and type

wmic useraccount get name,sid

You can even create a text file of the list by typing:

wmic useraccount get name,sid > usersid.txt

I actually imported my list into a NAV table (custom) so I could create a script to loop through them and recreate the NAV users if necessary.

Howdy, Stranger!

Quick Links

Categories

Nav 2013 - How to create user without accessing RTC

Answers