Cannot delete user after upgrade to NAV2013

Alex_Chow

This is after we upgraded from NAV2009 to NAV2013. When we tried to delete a user, it gives error:

Microsoft Dynamics NAV

---

The user "FAKEUSER" cannot be deleted because the user has been logged on to the system. You must set the user's state to "Disabled".

---

OK

---

I changed the State of the user to Disabled and it still gives this error. Interestingly, when I create a brand new user in NAV2013, it does not give this error when I delete them.

Anyone encountered this for upgrades?

avadlani

Hi, Did you ever find a solution to this issue. I am running into the same problem after upgrading a client to NAv2013.

Anu

mrioux

We had the same problem. with upgrading from Nav2009 to NAV2013 The way we fixed it was through the SQL Management Studio.

Make sure you note down which user you want to remove and their Security ID, and SID.

Example:

SID:
S-1-5-21-385447248-1272852142-134157935-10271
Security ID
16e116cc-2457-4529-8e80-e937864c30f7

Open up the SQL Instance

Open up 'Databases'>'NAV2013'>'Tables'>dbo.User

Right Click dbo.user and select Top 1000 Rows

Look for the user, then right click dbo > Script Table as > DELETE TO > New Query Editor Window

Enter Commands below:

USE [DatabaseName]
GO

DELETE
FROM dbo.[User]
WHERE [User Security ID] = 'actualSID'

DELETE
FROM dbo.[User Property]
WHERE [User Security ID] = 'actualSID'

DELETE
FROM dbo.[User Personalization]
WHERE [User SID] = 'actualSID'

DELETE
FROM dbo.[User Metadata]
WHERE [User SID] = 'actualSID'

DELETE
FROM dbo.[User Default Style Sheet]
WHERE [User ID] = 'actualSID'

DELETE
FROM dbo.[Page Data Personalization]
WHERE [User SID] = 'actualSID'

DELETE
FROM dbo.[Access Control]
WHERE [User Security ID] = 'actualSID'

After verifying the rows with the username, SID, or Security ID are gone from the dbo listed above, open up the NAV client, and see if the user exist. If it does, you can now click on the user and click on delete. It worked for us.

brunelli

I got the same issue today. Based on SQL Profiler, before deleting an user, NAV checks if a user personalization record for this user is still in the database. After deleting the user personalization record, I was able to delete the user. It seems, this has nothing to do with upgrade, because the database I tested was not an upgraded one.

Juhl

When you have logged in the first time, you cant delete a user. This is per design in 2013 and 2013R2, i dont know why. At least i dont think it is a bug.
Maybe it is related to auditing or something.

Just disable them or use the sql-way.

geordie

Juhl wrote:

When you have logged in the first time, you cant delete a user. This is per design in 2013 and 2013R2, i dont know why. At least i dont think it is a bug.
Maybe it is related to auditing or something.

Is this related to the error "Table User blocked" when trying to delete a user in NAV 2013 who did at least one access?

alvi99

This is not an error. The reason for this functionality is to keep the users history. If someone has posted something you must be able to look up this user...

Alex_Chow

alvi99 wrote:

This is not an error. The reason for this functionality is to keep the users history. If someone has posted something you must be able to look up this user...

Even if the user has not posted anything?

alvi99

Even if the user has not posted anything?

By design it is not allowed to delete a user after the user has created a profile, indicating activity in NAV, this is for audit purposes. NAV is checking for Personalization as an indication of activity, so assuming the user is no longer needed, removing the personalization (table 2000000073) for the user in the database should allow for deletion (I have not tested this)

Another solution is to just create a filter (in code) on page 9800 Users, which by default makes sure that disabled users are not shown.

OnOpenPage()
SETFILTER(State,'=%1',State::Enabled);

Br,
Alvi

Alex_Chow

brunelli wrote:

I got the same issue today. Based on SQL Profiler, before deleting an user, NAV checks if a user personalization record for this user is still in the database. After deleting the user personalization record, I was able to delete the user. It seems, this has nothing to do with upgrade, because the database I tested was not an upgraded one.

I can confirm that this solved the problem. :thumbsup:

Juhl

These are the tables involved, If you want to delete users.

This script i use to delete all user when needing to gain access to a Customer database, or so.

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]

But this is not what MS intended, as stated above.

omyvadiya

Deleting data for the user from 2 tables, did the trick for me:
1. User Personalizaition
2. User Metadata

:whistle: :whistle: :whistle:

Rikt-It

--***********************************************************************************
-- Christer Berntsson, Softronic, Stockholm, Sweden,2013-12-18
--
-- Look into and clean Nav201X-tables after Your moved database to other Domain
--
--
--***********************************************************************************


USE [YourDatabase] --<<<<<<<--- [Change to Your database]
GO

--Shows table-content
SELECT * FROM [dbo].[User]
SELECT * FROM [dbo].[Access Control]
SELECT * FROM [dbo].[User Property]
SELECT * FROM [dbo].[Page Data Personalization]
SELECT * FROM [dbo].[User Default Style Sheet]
SELECT * FROM [dbo].[User Metadata]
SELECT * FROM [dbo].[User Personalization]
--3 rows below, Use when move database to new NAV-server-instance
SELECT * FROM [dbo].[Server Instance]
SELECT * FROM [dbo].[Active Session]
SELECT * FROM [dbo].[Session Event]

/*
-- Mark rows and press F5 or press Execute to cleans tables.
--
TRUNCATE TABLE [dbo].[User]
TRUNCATE TABLE [dbo].[Access Control]
TRUNCATE TABLE [dbo].[User Property]
TRUNCATE TABLE [dbo].[Page Data Personalization]
TRUNCATE TABLE [dbo].[User Default Style Sheet]
TRUNCATE TABLE [dbo].[User Metadata]
TRUNCATE TABLE [dbo].[User Personalization]
--3 rows below, Use when move database to new NAV-server-instance
TRUNCATE TABLE [dbo].[Server Instance]
TRUNCATE TABLE [dbo].[Active Session]
TRUNCATE TABLE [dbo].[Session Event]

*/