Cannot delete user after upgrade to NAV2013

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.

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.

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?

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

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.

--***********************************************************************************
-- 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]

*/