New DataBase Creation Basic SQL Script

USE [master]
GO

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

GO

ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 100
GO

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

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


Use TEST
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 ('<ServerName>', 'TEST', 'MIS\gg_AppSupport', 'AppSupport', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_Tester', 'Tester', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', 'MIS\gg_Developer', 'Developer', 1)
insert into @UserList (Servername, DBName, LoginName, DBRole, IsNTLogin)
values ('<ServerName>', 'TEST', '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 Role Permissions
--Grant Tester Permissions
if @DBRole = N'Tester' Begin
EXEC sp_addrolemember N'db_datareader', N'Tester'
Grant View Definition to Tester
Grant execute to Tester
END
--Grant MDR_AppSupport Permissions
if @DBRole = N'AppSupport' Begin
EXEC sp_addrolemember N'db_datareader', N'AppSupport'
Grant View Definition to AppSupport
Grant execute to AppSupport
END

--Grant MDR_Developer Permissions
if @DBRole = N'MDR_Developer' Begin
EXEC sp_addrolemember N'db_datareader', N'MDR_Developer'
Grant View Definition to MDR_Developer
Grant execute to MDR_Developer
END
--Grant MDR_Designer Permissions
if @DBRole = N'Designer' and (@@SERVERNAME = N'<ServerName>' or @@SERVERNAME=N'<ServerName>')  Begin
EXEC sp_addrolemember N'db_owner', N'Designer'
END
ELSE Begin
EXEC sp_addrolemember N'db_datareader', N'Designer'
Grant View Definition to Designer
Grant execute to 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**'