web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / DB user got deleted
Microsoft Dynamics NAV (Archived)

DB user got deleted

(0) ShareShare
ReportReport
Posted on by Karthick23 90

Hi friends

 I have come across a weird issue in NAV classic SP1 environment.

Two DB users have got deleted from the system, I am not able to identify, when it was done, who has done it etc.

Is there any way to identify the changes that happened to the user records?

Thanks

Karthick

*This post is locked for comments

I have the same question (0)
  • Tobias Strobelt Profile Picture
    Tobias Strobelt 152 on at

    I´ve added a hyperlink to the original post to link to a stored procedure where you can get the value back :)

  • Verified answer
    Karthick23 Profile Picture
    Karthick23 90 on at

    Thank you so much for all the inputs and time spent on this. I had no choice except creating the same user again and moved on.

    Tobias - thanks for the detailed input, but unfortunately i am not able to retrieve any value. Will keep this in handy .

    thanks once again

  • Suggested answer
    Tobias Strobelt Profile Picture
    Tobias Strobelt 152 on at

    Hi Karthick,

    If the transaction log of your SQL-NAV-Database still exists you can run the following statement to see the "delete" tracking of your User table. Unfortunately you cannot see directly which data has been deleted as this is stored in binary form within the Transaction Log. If you need this Information in addition you can ask your SQL Server Admin to analyze the Row Content in detail for further Information which content has been deleted.

     

    SELECT deletes.AllocUnitName,deletes.Operation,l.[Begin Time],u.name
       FROM fn_dblog(NULL, NULL) l
    INNER JOIN ( SELECT [Transaction ID],AllocUnitName,[RowLog Contents 0],Operation
                              FROM fn_dblog(NULL, NULL) deletes2
                           WHERE (AllocUnitName = 'dbo.User.User$0'                 -- Database Login 
                                   OR AllocUnitName like 'dbo.$ndo$usrproperty%')   -- Windows Login
                                 AND Operation = 'LOP_DELETE_ROWS') deletes ON deletes.[Transaction ID] = l.[Transaction ID]
    INNER JOIN Master..sysusers u ON Convert(varchar(64),u.[sid],1) = Convert(varchar(64),[Transaction SID],1)
    WHERE l.Operation = 'LOP_BEGIN_XACT';

     

    But for your purpose it should fit to see when the user table has been touched for deletion and which user run the statement. For analyzing the binary content here´s an additional link:

    rusanu.com/.../how-to-read-and-interpret-the-sql-server-log

     

    In addition you can use the really helpfull stored procedure described in the following link.
    (credits to Muhammad Imran)

    http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

    Best regards,

    Tobias

  • Karthick23 Profile Picture
    Karthick23 90 on at

    Hi Sandeep

    Thanks for the input, it is not possible to find out in NAV, but is there any other workaround or any other method in SQL to find the log?

    Thanks

    Karthick

  • Community Member Profile Picture
    Community Member on at

    Hi karthick

    I think, there is no way to achieve this one.  You just contact you system admin if everybody don't have super permission.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans