Setting up a SQL Server database for use with DNN

I always forget the correct set of settings (especially user permissions) for the database when I am trying to throw up a quick DNN site. So I finally decided to script the database creation process.

The following 4 scripts will:

1. Create a login called DNNUSER
2. Create the database
3. Add a user called DNNUSER – attached to the login DNNUSER.
4. Will provide the user DNNUSER with the following roles:
   1. db_datawriter
   2. db_ddladmin
   3. db_securityadmin
   4. db_datareader

1. Create login DNNUSER

USE [master]
GO
CREATE LOGIN [DNNUSER] WITH PASSWORD=N'aSimplePassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

2. Create the database (change the name and paths to what you need)

CREATE DATABASE [DNN5] ON  PRIMARY 
( NAME = N'DNN5', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DNN5.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DNN5_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DNN5_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DNN5', @new_cmptlevel=90
GO
ALTER DATABASE [DNN5] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DNN5] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DNN5] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DNN5] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DNN5] SET ARITHABORT OFF 
GO
ALTER DATABASE [DNN5] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DNN5] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DNN5] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DNN5] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DNN5] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DNN5] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DNN5] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DNN5] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DNN5] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DNN5] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DNN5] SET  DISABLE_BROKER 
GO
ALTER DATABASE [DNN5] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DNN5] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DNN5] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DNN5] SET  READ_WRITE 
GO
ALTER DATABASE [DNN5] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [DNN5] SET  MULTI_USER 
GO
ALTER DATABASE [DNN5] SET PAGE_VERIFY CHECKSUM  
GO
USE [DNN5]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [DNN5] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

3. Create a DNNUSER user and add the user to the database with the appropriate roles

USE [DNN5]
GO
CREATE USER [DNNUSER] FOR LOGIN [DNNUSER]
GO
USE [DNN5]
GO
EXEC sp_addrolemember N'db_datawriter', N'DNNUSER'
GO
USE [DNN5]
GO
EXEC sp_addrolemember N'db_ddladmin', N'DNNUSER'
GO
USE [DNN5]
GO
EXEC sp_addrolemember N'db_securityadmin', N'DNNUSER'
GO
USE [DNN5]
GO
EXEC sp_addrolemember N'db_datareader', N'DNNUSER'
GO

4. Add the EXECUTE permission to the database

use [DNN5]
GO
GRANT EXECUTE TO [DNNUSER]
GO

Once the database has been setup – you should be able to fly through DNN’s installation.

Other stuff to setup:

Remember to provide the ASPNET user full control to the DNN folder and sub-folders (as the install will be adding files, modifying the web.config, etc) – done via the file system.