Thank you all for the detailed replies.  There are about 3 issues in the original post that I could have distinguished better.

First, as to foreign keys on the audit columns (CreatedByUserID and ModifiedByUserID)  Part of the reason for wanting the foreign keys on the audit tables is simply so we have a robust audit trail.  I need to be able to tie audited actions back to a particular user.  If I have to explain why some business transaction didn't work as expected, I want to know who last modified the record.  A foreign key will help to enforce this audit key, even if it is not a panacea.   I have worked on a complex database that doesn't do a good job of maintaining records of users over time.  It is a miserable experience and I want to use every integrity constraint possible to keep the data valid in all future projects.  I want all transactions ACID, that includes durable.  If the audit trail can be easily modified, it isn't quite durable as I can loose data to the accumulation of 'kruft' in the database.

Second, I wanted to share the SQL to find 'missing keys'.  In most cases, a table that has a column named ModuleID has a foreign key referencing the Modules table.  The query quickly finds the tables that do not.  I hope this is useful to the developers responsible for QA in the project.  If I can, I want to help open source projects, if only by making suggestions to the people that do the real work of updating the source code.  The SQL finds missing keys efficiently and it is easy to modify to find other missing keys.  I'm using similar SQL on a different SQL Server project and it is saving me many hours in systematically finding 'missing keys'.

Finally, there is a new CMS named weceem that is an open source grails project.  It has some great features and one that I really like is the the relational database schema implements a foreign key constraint on every relation.  This is enforced automatically by the use of GORM, the groovy object relation mapper.  While weceem is not as mature as DNN, it is an example of the rich diversity and innovation found in OSS.  Since I liked the fact that is uses foreign keys 100% of the time, I thought I would help DNN to use foreign keys 100% of the time.  I view a missing foreign key as a design bug.  I want to be one of the many eye that make bugs shallow, to paraphrase Eric Raymond.

PS.  The discussing on hard delete of users has been helpful for both my understanding and our DNN project.