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**'
