POST-INSTALLATION CONFIGURATION DEFAULTS

-- ************ POST-INSTALLATION CONFIGURATION DEFAULTS **************

SET NOCOUNT ON
USE master
GO

-- ************ START SYSTEM DATABASE RECONFIGURATION **************
--  SIZE THE MASTER DATABASE
USE master
GO
ALTER DATABASE Master
MODIFY FILE
    (NAME = master,
    SIZE = 100MB,
    FILEGROWTH = 50MB)
GO
ALTER DATABASE Master
MODIFY FILE
       (NAME = mastlog,
       SIZE = 100MB,
    FILEGROWTH = 50MB)
GO

--  SIZE THE MSDB DATABASE
ALTER DATABASE MSDB
MODIFY FILE
    (NAME = MSDBData,
    SIZE = 150MB,
    FILEGROWTH = 50MB)
GO
ALTER DATABASE MSDB
MODIFY FILE
       (NAME = MSDBLog,
       SIZE = 100MB,
    FILEGROWTH = 50MB)
GO

--  SIZE THE TEMPDB DATABASE
ALTER DATABASE TempDB
MODIFY FILE
    (NAME = tempdev,
    SIZE = 250MB,
    FILEGROWTH = 50MB)
GO
ALTER DATABASE TempDB
MODIFY FILE
       (NAME = templog,
       SIZE = 250MB,
    FILEGROWTH = 250MB)
GO
-- ************ END SYSTEM DATABASE RECONFIGURATION **************


-- ************ START DBA GROUP RIGHTS **************
--  ADD XYZ GROUP LOGINS
IF NOT EXISTS (SELECT * FROM syslogins WHERE [name] = 'XYZ\MSSQLDBA')
BEGIN
    create login [XYZ\MSSQLDBA] from windows;
--    EXEC sp_grantlogin 'XYZ\MSSQLDBA'
END
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [name] = 'XYZ\MS SQL Admin')
BEGIN
    create login [XYZ\MS SQL Admin] from windows;
--    EXEC sp_grantlogin 'XYZ\MS SQL Admin'
END
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [name] = 'XYZ\NCG_SQLSERVERTOOLS_AP')
BEGIN
    create login [XYZ\NCG_SQLSERVERTOOLS_AP] from windows;
--    EXEC sp_grantlogin 'XYZ\NCG_SQLSERVERTOOLS_AP'
END
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [name] = 'ASIA\BAC_NCG_MSSQL_AP')
BEGIN
    create login [ASIA\BAC_NCG_MSSQL_AP] from windows;
--    EXEC sp_grantlogin 'ASIA\BAC_NCG_MSSQL_AP'
END
GO

IF NOT EXISTS (SELECT * FROM syslogins WHERE [name] = 'ASIA\BAC_NCG_MSSQL_QA')
BEGIN
    create login [ASIA\BAC_NCG_MSSQL_QA] from windows;
--    EXEC sp_grantlogin 'ASIA\BAC_NCG_MSSQL_QA'
END
GO

-- ADD XYZ GROUPS TO SYSADMIN
EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'XYZ\MSSQLDBA'
GO

EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'XYZ\MS SQL Admin'
GO

EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'XYZ\NCG_SQLSERVERTOOLS_AP'
GO

EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'ASIA\BAC_NCG_MSSQL_AP'
GO

EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'ASIA\BAC_NCG_MSSQL_QA'
GO
-- ************ END DBA GROUP RIGHTS **************

-- ************ other default settings ***********************
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

-- ************ START CLEANUP ***********************
-- REMOVE DEFAULT SECURITY GROUP
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = 'BUILTIN\ADMINISTRATORS')
BEGIN
    EXEC sp_revokelogin 'BUILTIN\ADMINISTRATORS'
END
GO

-- REMOVE NT AUTHORITY\SYSTEM
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = 'NT AUTHORITY\SYSTEM')
BEGIN
    EXEC sp_revokelogin 'NT AUTHORITY\SYSTEM'
END
GO
 
-- remove guest access from databases
USE msdb
GO
IF EXISTS (SELECT * FROM sysusers WHERE [name] = 'guest')
BEGIN
    EXEC sp_revokedbaccess 'guest'
END
GO

-- ************ END CLEANUP ***********************

SET NOCOUNT OFF
GO