DotNetNuke Security Tip

The DotNetNuke installation instructions I have seen recommend creating a new database user and then assigning the db_owner role to that user. This recommendation is even included the new book by Shaun Walker et al, Professional DotNetNuke 4: Open Source Web Application Framework for ASP.NET 2.0 (Programmer to Programmer).

I didn't feel comfortable doing that so I searched on Google and found Shawn Mehaffie's blog article about the Most Under-Used Security Feature in DNN. Unfortunately, it appears his recommendation only works if using Sql Server Security. I prefer using Windows Authentication, and I believe that is the better practice (when it can be used).

Therefore, my solution was as follows:

  1. Give the new user the db_owner rights only for the installation of DNN, and then remove those rights.
  2. Grant execute permissions on all the sprocs and user defined functions to public.

In order to make step 2 as painless as possible (because it would indeed be painful to have to go through each sproc one-by-one to grant permissions), I created the following script. To use this, run it against your DotNetNuke DB (which I have assumed you named 'DotNetNuke') and then copy the output and paste it into a new query window and execute it.

use "DotNetNuke"

GO
declare @sqlstring nvarchar(1000)
declare sprocGrants cursor for
    select 'GRANT EXECUTE ON ' + NAME + ' TO PUBLIC'
    from sysobjects
    where xtype = 'P'

open sprocGrants
fetch next from sprocGrants into @sqlstring
while @@fetch_status = 0
  begin
    print @sqlstring
    exec sp_executesql @sqlstring
    fetch next from sprocGrants into @sqlstring
  end
close
sprocGrants
deallocate sprocGrants
GO

The output will look like this:

GRANT EXECUTE ON dnn_UserDefinedTable_UpdateField TO PUBLIC
GRANT EXECUTE ON dnn_Store_Administration_GetStoreInfo TO PUBLIC
GRANT EXECUTE ON dnn_GetEventLogPendingNotif TO PUBLIC

The output is 750 lines in version 4.3.5 on my machine, so the script does save a lot of typing ;)

Just copy the output and paste it into a new query window and execute it against the DNN database.