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:
- Give the new user the db_owner rights only for the installation of DNN, and then remove those rights.
- 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.