Script to Create a DataBase

USE [master]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
BEGIN
CREATE DATABASE TestDB ON  PRIMARY 
( NAME = N'TestDB',
FILENAME = N'G:\MSSQL10.SS2008\MSSQL\Data\TestDB.mdf',
SIZE = 1024 MB,
MAXSIZE = Unlimited,
FILEGROWTH = 10% )
 LOG ON 
( NAME = N'TestDB_Log',
FILENAME = N'H:\MSSQL10.SS2008\MSSQL\Data\TestDB_log.ldf',
SIZE = 1024 MB,
MAXSIZE = 2 TB,
 FILEGROWTH = 10% )
END

GO

ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC TestDB.[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE TestDB SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE TestDB SET ANSI_NULLS OFF 
GO
ALTER DATABASE TestDB SET ANSI_PADDING OFF 
GO
ALTER DATABASE TestDB SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE TestDB SET ARITHABORT OFF 
GO
ALTER DATABASE TestDB SET AUTO_CLOSE OFF 
GO
ALTER DATABASE TestDB SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE TestDB SET AUTO_SHRINK OFF 
GO
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE TestDB SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE TestDB SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE TestDB SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE TestDB SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE TestDB SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE TestDB SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE TestDB SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE TestDB SET TRUSTWORTHY OFF 
GO
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE TestDB SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE TestDB SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE TestDB SET  READ_WRITE 
GO
ALTER DATABASE TestDB SET RECOVERY SIMPLE 
GO
ALTER DATABASE TestDB SET  MULTI_USER 
GO
ALTER DATABASE TestDB SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE TestDB SET DB_CHAINING OFF 
GO
/*End Create Database Section */


Use TestDB
GO

/*Create MDR Security*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'AppSupport' AND type = 'R')
CREATE ROLE [AppSupport] AUTHORIZATION [dbo]
GO

--Create MDR_Designer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Designer' AND type = 'R')
CREATE ROLE [Designer] AUTHORIZATION [dbo]
GO

--Create MDR_Developer Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R')
CREATE ROLE [Developer] AUTHORIZATION [dbo]
GO

--Create MDR_Tester Role
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Tester' AND type = 'R')
CREATE ROLE [Tester] AUTHORIZATION [dbo]
GO

PRINT '**Begin DB User Additions**'
/*=================================================================*/
-- ADD USERS AND ROLES for Database

declare @UserList table 
( ID int IDENTITY(1, 1) NOT NULL,
ServerName varchar(250) NOT NULL, 
DBName varchar(250) NOT NULL, 
LoginName varchar(250) NOT NULL, 
DBRole varchar(250) NOT NULL,
IsNTLogin bit NOT NULL,
SQLPWD varchar(50) NULL
)

/*-------------------- Custom Data Here ---------------------------*/
/*Add Groups*/
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_AppSupport', 'AppSupport', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Tester', 'Tester', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Developer', 'Developer', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('ddwdb01cs', 'TestDB', 'MIS\gg_Designer', 'Designer', 1)

/*-----------------------------------------------------------------*/

declare @ID int
declare @ServerName varchar(250)
declare @DBName varchar(250)
declare @LoginName varchar(250)
declare @DBUserName varchar(250)
declare @DBRole varchar(250)
declare @IsNT bit
declare @PrevID int
declare @PWD varchar(50)

Set @ID = 0

WHILE @ID is NOT NULL
BEGIN
SELECT top 1 @ID = ID, @ServerName = ServerName, @DBName = DBName, @LoginName = LoginName, @DBRole = DBRole, @IsNT = IsNTLogin, @PWD = SQLPWD
FROM @UserList
WHERE ServerName = @@SERVERNAME and DB_Name() like DBName and ID > @ID
ORDER BY ID

print @ServerName + ' ' + @DBName + ' ' + @LoginName + ' ' + @DBRole

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = @LoginName)
IF @IsNT = 1
EXEC master.dbo.sp_grantlogin @loginame = @LoginName
ELSE
EXEC master.dbo.sp_addlogin @loginame = @LoginName, @passwd = @PWD

set @DBUserName = (SELECT U.name FROM dbo.sysusers U join master.dbo.syslogins L on L.sid = U.sid WHERE L.loginname = @LoginName)

IF @DBUserName IS NULL BEGIN
EXEC dbo.sp_grantdbaccess @loginame = @LoginName, @name_in_db = @LoginName
SET @DBUserName = @LoginName
END

EXEC dbo.sp_addrolemember @DBRole, @DBUserName

IF @PrevID = @ID
set @ID = NULL
ELSE
set @PrevID = @ID



--Grant MDR Role Permissions
--Grant MDR_Tester Permissions
if @DBRole = N'Tester' Begin
EXEC sp_addrolemember N'db_datareader', N'Tester'
Grant View Definition to MDR_Tester
Grant execute to MDR_Tester
END
--Grant MDR_AppSupport Permissions
if @DBRole = N'AppSupport' Begin
EXEC sp_addrolemember N'db_datareader', N'AppSupport'
Grant View Definition to MDR_AppSupport
Grant execute to MDR_AppSupport
END

--Grant MDR_Developer Permissions
if @DBRole = N'Developer' Begin
EXEC sp_addrolemember N'db_datareader', N'Developer'
Grant View Definition to MDR_Developer
Grant execute to MDR_Developer
END
--Grant MDR_Designer Permissions
if @DBRole = N'Designer' and (@@SERVERNAME = N'DDWDB01CS' or @@SERVERNAME=N'DDWSBDB01CS')  Begin
EXEC sp_addrolemember N'db_owner', N'Designer'
END
ELSE Begin
EXEC sp_addrolemember N'db_datareader', N'Designer'
Grant View Definition to MDR_Designer
Grant execute to MDR_Designer
End 
END

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

/*server level permissions*/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE LOGIN [MIS\svc_CS_SS2008Agent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/*db level permissions*/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MIS\svc_CS_SS2008Agent')
CREATE USER [MIS\svc_CS_SS2008Agent] FOR LOGIN [MIS\svc_CS_SS2008Agent] WITH DEFAULT_SCHEMA=[dbo]
GO

/*Apply permissions*/
EXEC sp_addrolemember N'db_owner', N'MIS\svc_CS_SS2008Agent'

USE [master]
GO
GRANT VIEW SERVER STATE TO [MIS\gg_AppSupport]
GRANT VIEW SERVER STATE TO [MIS\gg_Designer]
GRANT VIEW SERVER STATE TO [MIS\gg_Developer]
GRANT VIEW SERVER STATE TO [MIS\gg_Tester]
GO

PRINT '**DB User Addition Script complete**'