Auditing User Activity using DNN database

Overview

A customer may want to know where to look for information to audit users' activity using the database and especially in what SQL table it is possible to find information on creating, deleting, and the existing users. Queries against this table should be able to show, for instance, who was the database user that created an account in a production environment, which level of access they have, which portals they were granted access, if the account was authorized, and the source IP the request was originated.

Step by Step Guide

Database users are all documented in the "USERS" table, where you can query for columns like "LastIPAddress" (for most recent connection), "IsDeleted" (active or deleted user) and "IsSuperUser" along with "CreatedByUserID" (who added that specific user).

Additionally, the table "USERPORTALS" also provides an "IsAuthorized" flag for each portal, where we have more information about who has authorized access.

1. Edit this following SQL query, replacing "host" with the actual username you wish to audit:

SELECT Users.DisplayName, Users.Email, Users.IsDeleted, Users.CreatedByUserID, Users.CreatedOnDate, 
       Users.LastModifiedByUserID, Users.LastModifiedOnDate, aspnet_Users.IsAnonymous, 
       aspnet_Membership.IsApproved, aspnet_Membership.IsLockedOut, Users.LastIPAddress, 
       aspnet_Membership.LastLoginDate, aspnet_Users.LastActivityDate, UserPortals.UserPortalId
FROM (((((Users INNER JOIN aspnet_Users ON Users.Username = aspnet_Users.Username)
INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId)
INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID)
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID)
INNER JOIN UserPortals ON Users.UserID = UserPortals.UserId)
WHERE Users.UserName = 'host'

2. Run the query against your database, connected as a SuperUser account to check the desired information.

Content Author: Radu Vaduva