-- !!! Important !!!
-- Please goto line 76 / line 81 / line 431 / line 724, and make some necessary changes
--
-- Configuration script for Data SBUs SQL Server 2005 servers
--
--
-- Remember to first partition disks, create \MSSQL.1\MSSQL\Data directories
--
-- Configuring system databases...
USE master
GO
ALTER DATABASE master
MODIFY FILE
(NAME = master, SIZE = 128)
GO
ALTER DATABASE master
MODIFY FILE
(NAME = master, FILEGROWTH = 32)
GO
ALTER DATABASE master
MODIFY FILE
(NAME = mastlog, SIZE = 32)
GO
ALTER DATABASE master
MODIFY FILE
(NAME = mastlog, FILEGROWTH = 8)
GO
ALTER DATABASE model
MODIFY FILE
(NAME = modeldev, SIZE = 8)
GO
ALTER DATABASE model
MODIFY FILE
(NAME = modeldev, FILEGROWTH = 2)
GO
ALTER DATABASE model
MODIFY FILE
(NAME = modellog, SIZE = 8)
GO
ALTER DATABASE model
MODIFY FILE
(NAME = modellog, FILEGROWTH = 2)
GO
ALTER DATABASE model SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE msdb
MODIFY FILE
(NAME = MSDBData, SIZE = 128)
GO
ALTER DATABASE msdb
MODIFY FILE
(NAME = MSDBData, FILEGROWTH = 32)
GO
ALTER DATABASE msdb
MODIFY FILE
(NAME = MSDBLog, SIZE = 32)
GO
ALTER DATABASE msdb
MODIFY FILE
(NAME = MSDBLog, FILEGROWTH = 8)
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'E:\MSSQL\MSSQL.1\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'E:\MSSQL\MSSQL.1\Log\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, SIZE = 1024, MAXSIZE = UNLIMITED, FILEGROWTH = 128)
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, SIZE = 256, MAXSIZE = UNLIMITED, FILEGROWTH = 32)
GO
--
-- Configure system access and maintenance
--
-- Drop sample database(s)
IF DB_ID('AdventureWorks') IS NOT NULL
BEGIN
DROP DATABASE AdventureWorks
END
IF DB_ID('AdventureWorksDW') IS NOT NULL
BEGIN
DROP DATABASE AdventureWorksDW
END
GO
-- Role public_supplement definition
CREATE ROLE public_supplement
GO
GRANT EXECUTE ON sys.sp_MSSQLDMO90_version TO public_supplement
GRANT EXECUTE ON sys.sp_MSSQLDMO80_version TO public_supplement
GRANT EXECUTE ON sys.sp_MSSQLDMO70_version TO public_supplement
GRANT EXECUTE ON sys.sp_MSSQLOLE65_version TO public_supplement
GRANT EXECUTE ON sys.sp_MSSQLOLE_version TO public_supplement
GO
GRANT EXECUTE ON sys.sp_help TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_catalogs TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_catalogs_cursor TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_columns TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_columns_cursor TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_tables TO public_supplement
GRANT EXECUTE ON sys.sp_help_fulltext_tables_cursor TO public_supplement
GRANT EXECUTE ON sys.sp_help_publication_access TO public_supplement
GRANT EXECUTE ON sys.sp_helparticle TO public_supplement
GRANT EXECUTE ON sys.sp_helparticlecolumns TO public_supplement
GRANT EXECUTE ON sys.sp_helpconstraint TO public_supplement
GRANT EXECUTE ON sys.sp_helpdb TO public_supplement
GRANT EXECUTE ON sys.sp_helpdbfixedrole TO public_supplement
GRANT EXECUTE ON sys.sp_helpdevice TO public_supplement
GRANT EXECUTE ON sys.sp_helpdistpublisher TO public_supplement
GRANT EXECUTE ON sys.sp_helpdistributiondb TO public_supplement
GRANT EXECUTE ON sys.sp_helpdistributor TO public_supplement
GRANT EXECUTE ON sys.sp_helpextendedproc TO public_supplement
GRANT EXECUTE ON sys.sp_helpfile TO public_supplement
GRANT EXECUTE ON sys.sp_helpfilegroup TO public_supplement
GRANT EXECUTE ON sys.sp_helpgroup TO public_supplement
GRANT EXECUTE ON sys.sp_helpindex TO public_supplement
GRANT EXECUTE ON sys.sp_helplanguage TO public_supplement
GRANT EXECUTE ON sys.sp_helplinkedsrvlogin TO public_supplement
GRANT EXECUTE ON sys.sp_helplogins TO public_supplement
GRANT EXECUTE ON sys.sp_helpmergearticle TO public_supplement
GRANT EXECUTE ON sys.sp_helpmergefilter TO public_supplement
GRANT EXECUTE ON sys.sp_helpmergepublication TO public_supplement
GRANT EXECUTE ON sys.sp_helpmergesubscription TO public_supplement
GRANT EXECUTE ON sys.sp_helpntgroup TO public_supplement
GRANT EXECUTE ON sys.sp_helppublication TO public_supplement
GRANT EXECUTE ON sys.sp_helppullsubscription TO public_supplement
GRANT EXECUTE ON sys.sp_helpremotelogin TO public_supplement
GRANT EXECUTE ON sys.sp_helpreplicationdb TO public_supplement
GRANT EXECUTE ON sys.sp_helpreplicationdboption TO public_supplement
GRANT EXECUTE ON sys.sp_helpreplicationoption TO public_supplement
GRANT EXECUTE ON sys.sp_helprole TO public_supplement
GRANT EXECUTE ON sys.sp_helprolemember TO public_supplement
GRANT EXECUTE ON sys.sp_helprotect TO public_supplement
GRANT EXECUTE ON sys.sp_helpserver TO public_supplement
GRANT EXECUTE ON sys.sp_helpsort TO public_supplement
GRANT EXECUTE ON sys.sp_helpsrvrole TO public_supplement
GRANT EXECUTE ON sys.sp_helpsrvrolemember TO public_supplement
GRANT EXECUTE ON sys.sp_helpsubscriberinfo TO public_supplement
GRANT EXECUTE ON sys.sp_helpsubscription TO public_supplement
GRANT EXECUTE ON sys.sp_helpsubscription_properties TO public_supplement
GRANT EXECUTE ON sys.sp_helptext TO public_supplement
GRANT EXECUTE ON sys.sp_helptrigger TO public_supplement
GRANT EXECUTE ON sys.sp_helpuser TO public_supplement
GO
GRANT SELECT ON sys.objects TO public_supplement
GRANT SELECT ON sys.sysobjects TO public_supplement -- A view for the SQL 2000 system table
GRANT SELECT ON sys.sql_modules TO public_supplement
GRANT SELECT ON sys.syscomments TO public_supplement -- A view for the SQL 2000 system table
GRANT SELECT ON sys.server_permissions TO public_supplement
GO
REVOKE EXECUTE ON sys.sp_MSSQLDMO90_version FROM public
REVOKE EXECUTE ON sys.sp_MSSQLDMO80_version FROM public
REVOKE EXECUTE ON sys.sp_MSSQLDMO70_version FROM public
REVOKE EXECUTE ON sys.sp_MSSQLOLE65_version FROM public
REVOKE EXECUTE ON sys.sp_MSSQLOLE_version FROM public
GO
REVOKE EXECUTE ON sys.sp_help FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_catalogs FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_catalogs_cursor FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_columns FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_columns_cursor FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_tables FROM public
REVOKE EXECUTE ON sys.sp_help_fulltext_tables_cursor FROM public
REVOKE EXECUTE ON sys.sp_help_publication_access FROM public
REVOKE EXECUTE ON sys.sp_helparticle FROM public
REVOKE EXECUTE ON sys.sp_helparticlecolumns FROM public
REVOKE EXECUTE ON sys.sp_helpconstraint FROM public
REVOKE EXECUTE ON sys.sp_helpdb FROM public
REVOKE EXECUTE ON sys.sp_helpdbfixedrole FROM public
REVOKE EXECUTE ON sys.sp_helpdevice FROM public
REVOKE EXECUTE ON sys.sp_helpdistpublisher FROM public
REVOKE EXECUTE ON sys.sp_helpdistributiondb FROM public
REVOKE EXECUTE ON sys.sp_helpdistributor FROM public
REVOKE EXECUTE ON sys.sp_helpextendedproc FROM public
REVOKE EXECUTE ON sys.sp_helpfile FROM public
REVOKE EXECUTE ON sys.sp_helpfilegroup FROM public
REVOKE EXECUTE ON sys.sp_helpgroup FROM public
REVOKE EXECUTE ON sys.sp_helpindex FROM public
REVOKE EXECUTE ON sys.sp_helplanguage FROM public
REVOKE EXECUTE ON sys.sp_helplinkedsrvlogin FROM public
REVOKE EXECUTE ON sys.sp_helplogins FROM public
REVOKE EXECUTE ON sys.sp_helpmergearticle FROM public
REVOKE EXECUTE ON sys.sp_helpmergefilter FROM public
REVOKE EXECUTE ON sys.sp_helpmergepublication FROM public
REVOKE EXECUTE ON sys.sp_helpmergesubscription FROM public
REVOKE EXECUTE ON sys.sp_helpntgroup FROM public
REVOKE EXECUTE ON sys.sp_helppublication FROM public
REVOKE EXECUTE ON sys.sp_helppullsubscription FROM public
REVOKE EXECUTE ON sys.sp_helpremotelogin FROM public
REVOKE EXECUTE ON sys.sp_helpreplicationdb FROM public
REVOKE EXECUTE ON sys.sp_helpreplicationdboption FROM public
REVOKE EXECUTE ON sys.sp_helpreplicationoption FROM public
REVOKE EXECUTE ON sys.sp_helprole FROM public
REVOKE EXECUTE ON sys.sp_helprolemember FROM public
REVOKE EXECUTE ON sys.sp_helprotect FROM public
REVOKE EXECUTE ON sys.sp_helpserver FROM public
REVOKE EXECUTE ON sys.sp_helpsort FROM public
REVOKE EXECUTE ON sys.sp_helpsrvrole FROM public
REVOKE EXECUTE ON sys.sp_helpsrvrolemember FROM public
REVOKE EXECUTE ON sys.sp_helpsubscriberinfo FROM public
REVOKE EXECUTE ON sys.sp_helpsubscription FROM public
REVOKE EXECUTE ON sys.sp_helpsubscription_properties FROM public
REVOKE EXECUTE ON sys.sp_helptext FROM public
REVOKE EXECUTE ON sys.sp_helptrigger FROM public
REVOKE EXECUTE ON sys.sp_helpuser FROM public
REVOKE EXECUTE ON sys.xp_regread FROM public
REVOKE EXECUTE ON sys.xp_instance_regread FROM public
GO
DENY SELECT ON sys.objects TO public
DENY SELECT ON sys.sysobjects TO public -- A view for the SQL 2000 system table
DENY SELECT ON sys.sql_modules TO public
DENY SELECT ON sys.syscomments TO public -- A view for the SQL 2000 system table
GO
REVOKE SELECT ON sys.server_permissions FROM public
GO
CREATE ROLE rl_DBOwner
CREATE ROLE rl_DBDeveloper
CREATE ROLE rl_AppDeveloper
GO
EXEC sp_addrolemember 'rl_DBOwner', 'rl_DBDeveloper'
EXEC sp_addrolemember 'rl_DBOwner', 'rl_AppDeveloper'
GO
GRANT EXECUTE ON sp_help TO rl_DBOwner
GRANT EXECUTE ON sp_help_agent_default TO rl_DBOwner
GRANT EXECUTE ON sp_help_agent_parameter TO rl_DBOwner
GRANT EXECUTE ON sp_help_agent_profile TO rl_DBOwner
GRANT EXECUTE ON sp_help_datatype_mapping TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_catalog_components TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_catalogs TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_catalogs_cursor TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_columns TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_columns_cursor TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_system_components TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_tables TO rl_DBOwner
GRANT EXECUTE ON sp_help_fulltext_tables_cursor TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_alert_job TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_monitor TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_monitor_primary TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_monitor_secondary TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_primary_database TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_primary_secondary TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_secondary_database TO rl_DBOwner
GRANT EXECUTE ON sp_help_log_shipping_secondary_primary TO rl_DBOwner
GRANT EXECUTE ON sp_help_publication_access TO rl_DBOwner
GRANT EXECUTE ON sp_helpallowmerge_publication TO rl_DBOwner
GRANT EXECUTE ON sp_helparticle TO rl_DBOwner
GRANT EXECUTE ON sp_helparticlecolumns TO rl_DBOwner
GRANT EXECUTE ON sp_helparticledts TO rl_DBOwner
GRANT EXECUTE ON sp_helpconstraint TO rl_DBOwner
GRANT EXECUTE ON sp_helpdatatypemap TO rl_DBOwner
GRANT EXECUTE ON sp_helpdb TO rl_DBOwner
GRANT EXECUTE ON sp_helpdbfixedrole TO rl_DBOwner
GRANT EXECUTE ON sp_helpdevice TO rl_DBOwner
GRANT EXECUTE ON sp_helpdistpublisher TO rl_DBOwner
GRANT EXECUTE ON sp_helpdistributiondb TO rl_DBOwner
GRANT EXECUTE ON sp_helpdistributor TO rl_DBOwner
GRANT EXECUTE ON sp_helpdistributor_properties TO rl_DBOwner
GRANT EXECUTE ON sp_helpdynamicsnapshot_job TO rl_DBOwner
GRANT EXECUTE ON sp_helpextendedproc TO rl_DBOwner
GRANT EXECUTE ON sp_helpfile TO rl_DBOwner
GRANT EXECUTE ON sp_helpfilegroup TO rl_DBOwner
GRANT EXECUTE ON sp_helpgroup TO rl_DBOwner
GRANT EXECUTE ON sp_helpindex TO rl_DBOwner
GRANT EXECUTE ON sp_helplanguage TO rl_DBOwner
GRANT EXECUTE ON sp_helplinkedsrvlogin TO rl_DBOwner
GRANT EXECUTE ON sp_helplogins TO rl_DBOwner
GRANT EXECUTE ON sp_helplogreader_agent TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergealternatepublisher TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergearticle TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergearticlecolumn TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergearticleconflicts TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergeconflictrows TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergedeleteconflictrows TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergefilter TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergelogfiles TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergelogfileswithdata TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergelogsettings TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergepartition TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergepublication TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergepullsubscription TO rl_DBOwner
GRANT EXECUTE ON sp_helpmergesubscription TO rl_DBOwner
GRANT EXECUTE ON sp_helpntgroup TO rl_DBOwner
GRANT EXECUTE ON sp_helppeerrequests TO rl_DBOwner
GRANT EXECUTE ON sp_helppeerresponses TO rl_DBOwner
GRANT EXECUTE ON sp_helppublication TO rl_DBOwner
GRANT EXECUTE ON sp_helppublication_snapshot TO rl_DBOwner
GRANT EXECUTE ON sp_helppublicationsync TO rl_DBOwner
GRANT EXECUTE ON sp_helppullsubscription TO rl_DBOwner
GRANT EXECUTE ON sp_helpqreader_agent TO rl_DBOwner
GRANT EXECUTE ON sp_helpremotelogin TO rl_DBOwner
GRANT EXECUTE ON sp_helpreplfailovermode TO rl_DBOwner
GRANT EXECUTE ON sp_helpreplicationdb TO rl_DBOwner
GRANT EXECUTE ON sp_helpreplicationdboption TO rl_DBOwner
GRANT EXECUTE ON sp_helpreplicationoption TO rl_DBOwner
GRANT EXECUTE ON sp_helprole TO rl_DBOwner
GRANT EXECUTE ON sp_helprolemember TO rl_DBOwner
GRANT EXECUTE ON sp_helprotect TO rl_DBOwner
GRANT EXECUTE ON sp_helpserver TO rl_DBOwner
GRANT EXECUTE ON sp_helpsort TO rl_DBOwner
GRANT EXECUTE ON sp_helpsrvrole TO rl_DBOwner
GRANT EXECUTE ON sp_helpsrvrolemember TO rl_DBOwner
GRANT EXECUTE ON sp_helpstats TO rl_DBOwner
GRANT EXECUTE ON sp_helpsubscriberinfo TO rl_DBOwner
GRANT EXECUTE ON sp_helpsubscription TO rl_DBOwner
GRANT EXECUTE ON sp_helpsubscription_properties TO rl_DBOwner
GRANT EXECUTE ON sp_helpsubscriptionerrors TO rl_DBOwner
GRANT EXECUTE ON sp_helptext TO rl_DBOwner
GRANT EXECUTE ON sp_helptracertokenhistory TO rl_DBOwner
GRANT EXECUTE ON sp_helptracertokens TO rl_DBOwner
GRANT EXECUTE ON sp_helptrigger TO rl_DBOwner
GRANT EXECUTE ON sp_helpuser TO rl_DBOwner
GRANT EXECUTE ON sp_helpxactsetjob TO rl_DBOwner
GO
-- Create logins
CREATE LOGIN DMClient WITH PASSWORD = '2Fetch(Get'
CREATE LOGIN DMEditor WITH PASSWORD = 'acct4Mods#'
GO
-- model
USE model
GO
-- Default users for each database
CREATE USER DMClient FOR LOGIN DMClient WITH DEFAULT_SCHEMA = dbo
CREATE USER DMEditor FOR LOGIN DMEditor WITH DEFAULT_SCHEMA = dbo
GO
-- Default roles for each database
CREATE ROLE public_supplement
CREATE ROLE rl_DBDeveloper
CREATE ROLE rl_DataReader
CREATE ROLE rl_DataWriter
CREATE ROLE rl_DataDownload
CREATE ROLE rl_DataUpload
CREATE ROLE rl_DBOwner
CREATE ROLE rl_AppDeveloper
GO
-- This role will be for programmers who need to read, write, create
-- stored procedures and grant permissions on stored procedures
EXEC sp_addrolemember 'db_owner', 'rl_DBOwner'
EXEC sp_addrolemember 'db_datareader', 'rl_DataReader'
EXEC sp_addrolemember 'db_datawriter', 'rl_DataWriter'
EXEC sp_addrolemember 'db_datareader', 'rl_DBDeveloper'
EXEC sp_addrolemember 'db_datawriter', 'rl_DBDeveloper'
EXEC sp_addrolemember 'rl_DataDownload', 'DMClient'
EXEC sp_addrolemember 'rl_DataUpload', 'DMEditor'
EXEC sp_addrolemember 'rl_DataReader', 'rl_AppDeveloper'
EXEC sp_addrolemember 'rl_DataWriter', 'rl_AppDeveloper'
GO
--
GRANT CREATE DEFAULT TO rl_DBDeveloper
GRANT CREATE FUNCTION TO rl_DBDeveloper
GRANT CREATE PROCEDURE TO rl_DBDeveloper
GRANT CREATE RULE TO rl_DBDeveloper
GRANT CREATE TABLE TO rl_DBDeveloper
GRANT CREATE VIEW TO rl_DBDeveloper
GO
GRANT CONTROL ON SCHEMA::dbo TO rl_DBDeveloper
GRANT VIEW DEFINITION ON SCHEMA::dbo TO rl_AppDeveloper
GO
DENY BACKUP DATABASE TO rl_DBDeveloper
DENY BACKUP LOG TO rl_DBDeveloper
GO
--
DENY CREATE DEFAULT TO rl_DataReader
DENY CREATE FUNCTION TO rl_DataReader
DENY CREATE PROCEDURE TO rl_DataReader
DENY CREATE RULE TO rl_DataReader
DENY CREATE TABLE TO rl_DataReader
DENY CREATE VIEW TO rl_DataReader
DENY BACKUP DATABASE TO rl_DataReader
DENY BACKUP LOG TO rl_DataReader
GO
--
DENY CREATE DEFAULT TO rl_DataWriter
DENY CREATE FUNCTION TO rl_DataWriter
DENY CREATE PROCEDURE TO rl_DataWriter
DENY CREATE RULE TO rl_DataWriter
DENY CREATE TABLE TO rl_DataWriter
DENY CREATE VIEW TO rl_DataWriter
DENY BACKUP DATABASE TO rl_DataWriter
DENY BACKUP LOG TO rl_DataWriter
GO
-- Grant rights to public_supplement on the SQL 2000 Compatibility Views
GRANT SELECT ON sys.syscolumns TO public_supplement
GRANT SELECT ON sys.syscomments TO public_supplement
GRANT SELECT ON sys.sysdepends TO public_supplement
GRANT SELECT ON sys.sysfilegroups TO public_supplement
GRANT SELECT ON sys.sysfiles TO public_supplement
GRANT SELECT ON sys.sysforeignkeys TO public_supplement
GRANT SELECT ON sys.sysfulltextcatalogs TO public_supplement
GRANT SELECT ON sys.sysindexes TO public_supplement
GRANT SELECT ON sys.sysindexkeys TO public_supplement
GRANT SELECT ON sys.sysmembers TO public_supplement
GRANT SELECT ON sys.sysobjects TO public_supplement
GRANT SELECT ON sys.syspermissions TO public_supplement
GRANT SELECT ON sys.sysprotects TO public_supplement
GRANT SELECT ON sys.sysreferences TO public_supplement
GRANT SELECT ON sys.systypes TO public_supplement
GRANT SELECT ON sys.sysusers TO public_supplement
-- Grant rights to public_supplement on the SQL 2005 system views (Catalog Views)
GRANT SELECT ON sys.columns TO public_supplement
GRANT SELECT ON sys.sql_modules TO public_supplement
GRANT SELECT ON sys.sql_dependencies TO public_supplement
GRANT SELECT ON sys.filegroups TO public_supplement
GRANT SELECT ON sys.database_files TO public_supplement
GRANT SELECT ON sys.foreign_keys TO public_supplement
GRANT SELECT ON sys.fulltext_catalogs TO public_supplement
GRANT SELECT ON sys.indexes TO public_supplement
GRANT SELECT ON sys.index_columns TO public_supplement
GRANT SELECT ON sys.database_role_members TO public_supplement
GRANT SELECT ON sys.objects TO public_supplement
GRANT SELECT ON sys.database_permissions TO public_supplement
GRANT SELECT ON sys.foreign_keys TO public_supplement
GRANT SELECT ON sys.types TO public_supplement
GRANT SELECT ON sys.database_principals TO public_supplement
GO
-- Revoke rights from public on the SQL 2000 Compatibility Views
REVOKE SELECT ON sys.syscolumns FROM public
REVOKE SELECT ON sys.syscomments FROM public
REVOKE SELECT ON sys.sysdepends FROM public
REVOKE SELECT ON sys.sysfilegroups FROM public
REVOKE SELECT ON sys.sysfiles FROM public
REVOKE SELECT ON sys.sysforeignkeys FROM public
REVOKE SELECT ON sys.sysfulltextcatalogs FROM public
REVOKE SELECT ON sys.sysindexes FROM public
REVOKE SELECT ON sys.sysindexkeys FROM public
REVOKE SELECT ON sys.sysmembers FROM public
REVOKE SELECT ON sys.sysobjects FROM public
REVOKE SELECT ON sys.syspermissions FROM public
REVOKE SELECT ON sys.sysprotects FROM public
REVOKE SELECT ON sys.sysreferences FROM public
REVOKE SELECT ON sys.systypes FROM public
REVOKE SELECT ON sys.sysusers FROM public
GO
-- Revoke rights from public on the SQL 2005 system views (Catalog Views)
REVOKE SELECT ON sys.columns FROM public
REVOKE SELECT ON sys.sql_modules FROM public
REVOKE SELECT ON sys.sql_dependencies FROM public
REVOKE SELECT ON sys.filegroups FROM public
REVOKE SELECT ON sys.database_files FROM public
REVOKE SELECT ON sys.foreign_keys FROM public
REVOKE SELECT ON sys.fulltext_catalogs FROM public
REVOKE SELECT ON sys.indexes FROM public
REVOKE SELECT ON sys.index_columns FROM public
REVOKE SELECT ON sys.database_role_members FROM public
REVOKE SELECT ON sys.objects FROM public
REVOKE SELECT ON sys.database_permissions FROM public
REVOKE SELECT ON sys.foreign_keys FROM public
REVOKE SELECT ON sys.types FROM public
REVOKE SELECT ON sys.database_principals FROM public
GO
-- DENY rights to rl_DataReader on the SQL 2000 system views
GRANT SELECT ON sys.syscolumns TO rl_DataReader
GRANT SELECT ON sys.syscomments TO rl_DataReader
GRANT SELECT ON sys.sysindexes TO rl_DataReader
GRANT SELECT ON sys.sysobjects TO rl_DataReader
GRANT SELECT ON sys.sysindexkeys TO rl_DataReader
GRANT SELECT ON sys.systypes TO rl_DataReader
DENY SELECT ON sys.sysdepends TO rl_DataReader
DENY SELECT ON sys.sysfilegroups TO rl_DataReader
DENY SELECT ON sys.sysfiles TO rl_DataReader
DENY SELECT ON sys.sysforeignkeys TO rl_DataReader
DENY SELECT ON sys.sysfulltextcatalogs TO rl_DataReader
DENY SELECT ON sys.sysmembers TO rl_DataReader
DENY SELECT ON sys.syspermissions TO rl_DataReader
DENY SELECT ON sys.sysprotects TO rl_DataReader
DENY SELECT ON sys.sysreferences TO rl_DataReader
DENY SELECT ON sys.sysusers TO rl_DataReader
-- DENY rights to rl_DataReader on the SQL 2005 system views (Catalog Views)
GRANT SELECT ON sys.sql_modules TO rl_DataReader
GRANT SELECT ON sys.columns TO rl_DataReader
GRANT SELECT ON sys.indexes TO rl_DataReader
GRANT SELECT ON sys.objects TO rl_DataReader
GRANT SELECT ON sys.index_columns TO rl_DataReader
GRANT SELECT ON sys.types TO rl_DataReader
DENY SELECT ON sys.sql_dependencies TO rl_DataReader
DENY SELECT ON sys.filegroups TO rl_DataReader
DENY SELECT ON sys.database_files TO rl_DataReader
DENY SELECT ON sys.foreign_keys TO rl_DataReader
DENY SELECT ON sys.fulltext_catalogs TO rl_DataReader
DENY SELECT ON sys.database_role_members TO rl_DataReader
DENY SELECT ON sys.database_permissions TO rl_DataReader
DENY SELECT ON sys.foreign_keys TO rl_DataReader
DENY SELECT ON sys.database_principals TO rl_DataReader
-- DENY rights to rl_DataWriter on the SQL 2000 system views
GRANT SELECT ON sys.syscolumns TO rl_DataWriter
GRANT SELECT ON sys.syscomments TO rl_DataWriter
GRANT SELECT ON sys.sysindexes TO rl_DataWriter
GRANT SELECT ON sys.sysobjects TO rl_DataWriter
GRANT SELECT ON sys.sysindexkeys TO rl_DataWriter
GRANT SELECT ON sys.systypes TO rl_DataWriter
DENY SELECT ON sys.sysdepends TO rl_DataWriter
DENY SELECT ON sys.sysfilegroups TO rl_DataWriter
DENY SELECT ON sys.sysfiles TO rl_DataWriter
DENY SELECT ON sys.sysforeignkeys TO rl_DataWriter
DENY SELECT ON sys.sysfulltextcatalogs TO rl_DataWriter
DENY SELECT ON sys.sysmembers TO rl_DataWriter
DENY SELECT ON sys.syspermissions TO rl_DataWriter
DENY SELECT ON sys.sysprotects TO rl_DataWriter
DENY SELECT ON sys.sysreferences TO rl_DataWriter
DENY SELECT ON sys.sysusers TO rl_DataWriter
-- DENY rights to rl_DataWriter on the SQL 2005 system views (Catalog Views)
GRANT SELECT ON sys.sql_modules TO rl_DataWriter
GRANT SELECT ON sys.columns TO rl_DataWriter
GRANT SELECT ON sys.indexes TO rl_DataWriter
GRANT SELECT ON sys.objects TO rl_DataWriter
GRANT SELECT ON sys.index_columns TO rl_DataWriter
GRANT SELECT ON sys.types TO rl_DataWriter
DENY SELECT ON sys.sql_dependencies TO rl_DataWriter
DENY SELECT ON sys.filegroups TO rl_DataWriter
DENY SELECT ON sys.database_files TO rl_DataWriter
DENY SELECT ON sys.foreign_keys TO rl_DataWriter
DENY SELECT ON sys.fulltext_catalogs TO rl_DataWriter
DENY SELECT ON sys.database_role_members TO rl_DataWriter
DENY SELECT ON sys.database_permissions TO rl_DataWriter
DENY SELECT ON sys.foreign_keys TO rl_DataWriter
DENY SELECT ON sys.database_principals TO rl_DataWriter
GO
-- DENY rights to rl_DataDownload on the SQL 2000 system views
DENY SELECT ON sys.syscolumns TO rl_DataDownload
DENY SELECT ON sys.syscomments TO rl_DataDownload
DENY SELECT ON sys.sysdepends TO rl_DataDownload
DENY SELECT ON sys.sysfilegroups TO rl_DataDownload
DENY SELECT ON sys.sysfiles TO rl_DataDownload
DENY SELECT ON sys.sysforeignkeys TO rl_DataDownload
DENY SELECT ON sys.sysfulltextcatalogs TO rl_DataDownload
DENY SELECT ON sys.sysindexes TO rl_DataDownload
DENY SELECT ON sys.sysindexkeys TO rl_DataDownload
DENY SELECT ON sys.sysmembers TO rl_DataDownload
DENY SELECT ON sys.sysobjects TO rl_DataDownload
DENY SELECT ON sys.syspermissions TO rl_DataDownload
DENY SELECT ON sys.sysprotects TO rl_DataDownload
DENY SELECT ON sys.sysreferences TO rl_DataDownload
DENY SELECT ON sys.systypes TO rl_DataDownload
DENY SELECT ON sys.sysusers TO rl_DataDownload
-- DENY rights to rl_DataDownload on the SQL 2005 system views (Catalog Views)
DENY SELECT ON sys.columns TO rl_DataDownload
DENY SELECT ON sys.sql_modules TO rl_DataDownload
DENY SELECT ON sys.sql_dependencies TO rl_DataDownload
DENY SELECT ON sys.filegroups TO rl_DataDownload
DENY SELECT ON sys.database_files TO rl_DataDownload
DENY SELECT ON sys.foreign_keys TO rl_DataDownload
DENY SELECT ON sys.fulltext_catalogs TO rl_DataDownload
DENY SELECT ON sys.indexes TO rl_DataDownload
DENY SELECT ON sys.index_columns TO rl_DataDownload
DENY SELECT ON sys.database_role_members TO rl_DataDownload
DENY SELECT ON sys.objects TO rl_DataDownload
DENY SELECT ON sys.database_permissions TO rl_DataDownload
DENY SELECT ON sys.foreign_keys TO rl_DataDownload
DENY SELECT ON sys.types TO rl_DataDownload
DENY SELECT ON sys.database_principals TO rl_DataDownload
GO
-- DENY rights to rl_DataUpload on the SQL 2000 system views
DENY SELECT ON sys.syscolumns TO rl_DataUpload
DENY SELECT ON sys.syscomments TO rl_DataUpload
DENY SELECT ON sys.sysdepends TO rl_DataUpload
DENY SELECT ON sys.sysfilegroups TO rl_DataUpload
DENY SELECT ON sys.sysfiles TO rl_DataUpload
DENY SELECT ON sys.sysforeignkeys TO rl_DataUpload
DENY SELECT ON sys.sysfulltextcatalogs TO rl_DataUpload
DENY SELECT ON sys.sysindexes TO rl_DataUpload
DENY SELECT ON sys.sysindexkeys TO rl_DataUpload
DENY SELECT ON sys.sysmembers TO rl_DataUpload
DENY SELECT ON sys.sysobjects TO rl_DataUpload
DENY SELECT ON sys.syspermissions TO rl_DataUpload
DENY SELECT ON sys.sysprotects TO rl_DataUpload
DENY SELECT ON sys.sysreferences TO rl_DataUpload
DENY SELECT ON sys.systypes TO rl_DataUpload
DENY SELECT ON sys.sysusers TO rl_DataUpload
-- DENY rights to rl_DataUpload on the SQL 2005 system views (Catalog Views)
DENY SELECT ON sys.columns TO rl_DataUpload
DENY SELECT ON sys.sql_modules TO rl_DataUpload
DENY SELECT ON sys.sql_dependencies TO rl_DataUpload
DENY SELECT ON sys.filegroups TO rl_DataUpload
DENY SELECT ON sys.database_files TO rl_DataUpload
DENY SELECT ON sys.foreign_keys TO rl_DataUpload
DENY SELECT ON sys.fulltext_catalogs TO rl_DataUpload
DENY SELECT ON sys.indexes TO rl_DataUpload
DENY SELECT ON sys.index_columns TO rl_DataUpload
DENY SELECT ON sys.database_role_members TO rl_DataUpload
DENY SELECT ON sys.objects TO rl_DataUpload
DENY SELECT ON sys.database_permissions TO rl_DataUpload
DENY SELECT ON sys.foreign_keys TO rl_DataUpload
DENY SELECT ON sys.types TO rl_DataUpload
DENY SELECT ON sys.database_principals TO rl_DataUpload
GO
--
-- security policy
--
-- Revoke guest access to msdb in order to keep any non system administrators from accessing
-- the database without explicit permissions.
USE msdb
GO
REVOKE CONNECT FROM guest
GO
-- Add DBA to the sysadmin group
USE master
GO
CREATE LOGIN [MSDOMAIN1\mxu1] FROM WINDOWS WITH DEFAULT_DATABASE = [tempdb]
CREATE LOGIN [MSDOMAIN1\ywang] FROM WINDOWS WITH DEFAULT_DATABASE = [tempdb]
GO
EXEC master.dbo.sp_addsrvrolemember @loginame = N'MSDOMAIN1\azhou', @rolename = N'sysadmin'
EXEC master.dbo.sp_addsrvrolemember @loginame = N'MSDOMAIN1\ywang', @rolename = N'sysadmin'
GO
-- NutsAndBolts.sql
USE master
GO
CREATE DATABASE NutsAndBolts
ON PRIMARY
( NAME = NutsAndBolts_PData,
FILENAME = "E:\MSSQL\MSSQL.1\Data\NutsAndBolts_Primary.mdf",
SIZE = 64,
MAXSIZE = UNLIMITED,
FILEGROWTH = 32 ),
FILEGROUP
NutsAndBolts_Data ( NAME = NutsAndBolts_Data1,
FILENAME = "E:\MSSQL\MSSQL.1\Data\NutsAndBolts_Data1.ndf",
SIZE = 128,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64 ),
( NAME = NutsAndBolts_Data2,
FILENAME = "E:\MSSQL\MSSQL.1\Data\NutsAndBolts_Data2.ndf",
SIZE = 128,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64 ),
FILEGROUP
NutsAndBolts_Index ( NAME = NutsAndBolts_Index1,
FILENAME = "E:\MSSQL\MSSQL.1\Data\NutsAndBolts_Index1.ndf",
SIZE = 32,
MAXSIZE = UNLIMITED,
FILEGROWTH = 32 ),
( NAME = NutsAndBolts_Index2,
FILENAME = "E:\MSSQL\MSSQL.1\Data\NutsAndBolts_Index2.ndf",
SIZE = 32,
MAXSIZE = UNLIMITED,
FILEGROWTH = 32 )
LOG ON
( NAME = NutsAndBolts_Log,
FILENAME = "E:\MSSQL\MSSQL.1\Log\NutsAndBolts_Log.ldf",
SIZE = 128,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64 )
GO
ALTER DATABASE NutsAndBolts SET RECOVERY SIMPLE
GO
ALTER DATABASE NutsAndBolts
SET DB_CHAINING ON
GO
USE NutsAndBolts
GO
EXECUTE sp_changedbowner 'sa'
GO
GRANT CONNECT TO guest
GO
IF OBJECT_ID('dbo.ErrorLog', 'U') IS NOT NULL
DROP TABLE dbo.ErrorLog
GO
--
-- Name: ErrorLog (Table)
-- Purpose: Store user defined error message and/or system error message
-- Location: At NutsAndBolts Database
-- Authorized to: None
-- Last Update :
-- Author:
-- Create date: 08/23/99
--
-- Revisions:
--
CREATE TABLE dbo.ErrorLog
(
Time DATETIME NOT NULL,
Err INT NOT NULL,
Msg VARCHAR(500) NOT NULL,
Id VARCHAR(50) NOT NULL,
DBName VARCHAR(30) NOT NULL,
ProcName VARCHAR(50) NOT NULL,
DBUser VARCHAR(20) NOT NULL,
HostName VARCHAR(20) NOT NULL,
Application VARCHAR(30) NOT NULL,
Checked BIT NOT NULL
) ON NutsAndBolts_Data
GO
-- Default constraint(s) definition
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_Time DEFAULT GETDATE() FOR Time
GO
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_DBName DEFAULT LEFT(DB_NAME(), 30) FOR DBName
GO
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_DBUser DEFAULT LEFT(SUSER_SNAME(), 20) FOR DBUser
GO
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_HostName DEFAULT LEFT(HOST_NAME(), 20) FOR HostName
GO
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_Application DEFAULT LEFT(RTRIM(LTRIM(APP_NAME())), 30) FOR Application
GO
ALTER TABLE dbo.ErrorLog ADD CONSTRAINT DF_ErrorLog_Checked DEFAULT 0 FOR Checked
GO
GRANT SELECT ON dbo.ErrorLog TO public
GO
IF OBJECT_ID('dbo.EventLog', 'U') IS NOT NULL
DROP TABLE dbo.EventLog
GO
--
-- Name: EventLog (Table)
-- Purpose:
-- Location: NutsAndBolts
-- Authorized to: None
-- Last Update :
-- Author: Michael .H
-- Create date: 2008-03-03
--
-- Revisions:
--
CREATE TABLE dbo.EventLog
(
Id INT IDENTITY(1, 1) NOT NULL,
EventData XML NOT NULL
) ON NutsAndBolts_Data
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(
Id ASC
) ON NutsAndBolts_Data
GO
CREATE PRIMARY XML INDEX XI_EventLog_EventData
ON dbo.EventLog
(
EventData
)
GO
CREATE XML INDEX XI_EventLog_EventData_Path
ON dbo.EventLog
(
EventData
)
USING XML INDEX XI_EventLog_EventData FOR PATH
GO
CREATE XML INDEX XI_EventLog_EventData_Value
ON dbo.EventLog
(
EventData
)
USING XML INDEX XI_EventLog_EventData FOR VALUE
GO
GRANT SELECT ON dbo.EventLog TO public
GO
USE NutsAndBolts
GO
IF OBJECT_ID('dbo.ProcUsage', 'U') IS NOT NULL
DROP TABLE dbo.ProcUsage
GO
CREATE TABLE dbo.ProcUsage
(
TrackType TINYINT,
DBName VARCHAR(30) NOT NULL,
ProcName VARCHAR(50) NOT NULL,
Id VARCHAR(15) NULL,
LastAccess DATETIME NOT NULL,
AccessCount INT NOT NULL,
DBUser VARCHAR(20) NOT NULL,
HostName VARCHAR(20) NOT NULL,
AppName VARCHAR(50) NOT NULL,
Checked BIT NOT NULL
) ON NutsAndBolts_Data
GO
-- Default constraint(s) definition
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_LastAccess DEFAULT GETDATE() FOR LastAccess
GO
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_AccessCount DEFAULT 1 FOR AccessCount
GO
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_DBUser DEFAULT LEFT(RTRIM(LTRIM(SUSER_SNAME())), 20) FOR DBUser
GO
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_HostName DEFAULT LEFT(RTRIM(LTRIM(HOST_NAME())), 20) FOR HostName
GO
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_AppName DEFAULT LEFT(RTRIM(LTRIM(APP_NAME())), 30) FOR AppName
GO
ALTER TABLE dbo.ProcUsage ADD CONSTRAINT DF_ProcUsage_Checked DEFAULT 0 FOR Checked
GO
CREATE NONCLUSTERED INDEX IX_ProcUsage_DBName_ProcName_Time
ON ProcUsage
(
TrackType,
DBName,
ProcName,
LastAccess
) ON NutsAndBolts_Data
GO
IF OBJECT_ID('dbo.AvailableDiskSpace', 'U') IS NOT NULL
DROP TABLE dbo.AvailableDiskSpace
GO
CREATE TABLE dbo.AvailableDiskSpace
(
Id INT IDENTITY (1, 1) NOT NULL,
LastUpdate SMALLDATETIME NOT NULL,
DriveLetter CHAR(1) NOT NULL,
FreeMB INT NOT NULL,
) ON NutsAndBolts_Data
GO
ALTER TABLE dbo.AvailableDiskSpace ADD CONSTRAINT DF_AvailableDiskSpace_LastUpdate DEFAULT GETDATE() FOR LastUpdate
GO
ALTER TABLE dbo.AvailableDiskSpace ADD CONSTRAINT PK_AvailableDiskSpace PRIMARY KEY
(
Id
) ON NutsAndBolts_Data
GO
-- This stored procedure captures the amount of free space on each disk
-- on the server. Results are stored in NutsAndBolts.dbo.AvailableDiskSpace.
--
-- A SQLAgent job should be created to execute this procedure on a regular basis
--
PRINT 'Create procedures...'
GO
IF OBJECT_ID('dbo.getAvailableDiskSpace', 'P') IS NOT NULL
DROP PROCEDURE dbo.getAvailableDiskSpace
GO
-- Procedure: getAvailableDiskSpace
-- Purpose: Get available disk space
-- Location: NutsAndBolts
-- Authorized to:
-- Last Update:
-- Parameter:
-- Input
-- None
-- Output
-- None
--
-- Result Set:
-- None
--
-- Return: @@ERROR
-- Exception return values: None
--
-- Author: John Panfil
-- Create Date: 11/18/2004
-- Revisions:
--
CREATE PROCEDURE dbo.getAvailableDiskSpace
AS
SET NOCOUNT ON
DECLARE @l_DiskFreeSpace INT,
@l_FreeSpaceThreshhold INT,
@l_DriveLetter CHAR(1),
@l_AlertMessage VARCHAR(200),
@l_MailSubject VARCHAR(100),
@l_Recipients VARCHAR(100)
/* If free space is less than this (in MB), alert somebody */
SET @l_FreeSpaceThreshhold = 1024
SET @l_AlertMessage = NULL
SET @l_Recipients = 'Africa.Zhou@morningstar.com'
/* Create a temp table to hold disk space information */
CREATE TABLE #disk_free_space
(
DriveLetter CHAR(1) NOT NULL,
FreeMB INT NOT NULL
)
INSERT INTO #disk_free_space
EXEC master.dbo.xp_fixeddrives
/* Save results for trend analysis */
INSERT INTO dbo.AvailableDiskSpace (DriveLetter, FreeMB)
SELECT DriveLetter, FreeMB
FROM #disk_free_space
/* Examine free space of each drive and send email for those that are with low */
DECLARE cur_DriveSpace CURSOR FAST_FORWARD FOR
SELECT DriveLetter, FreeMB
FROM #disk_free_space
OPEN cur_DriveSpace
FETCH NEXT FROM cur_DriveSpace INTO @l_DriveLetter, @l_DiskFreeSpace
/* Examine free space of each drive */
/* Build text of email for each drive that is low */
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @l_DiskFreeSpace < @l_FreeSpaceThreshhold
BEGIN
IF @l_AlertMessage IS NULL
BEGIN
SET @l_AlertMessage = @l_DriveLetter + ' has ' + CAST(@l_DiskFreeSpace AS VARCHAR) + ' MB free.'
END
ELSE
BEGIN
SET @l_AlertMessage = @l_AlertMessage + CHAR(13) + @l_DriveLetter + ' has ' + CAST(@l_DiskFreeSpace AS VARCHAR) + ' MB free.'
END
END
FETCH NEXT FROM cur_DriveSpace INTO @l_DriveLetter, @l_DiskFreeSpace
END
CLOSE cur_DriveSpace
DEALLOCATE cur_DriveSpace
DROP TABLE #disk_free_space
-- Send out email
IF @l_AlertMessage IS NOT NULL
BEGIN
SET @l_MailSubject = 'Free disk space is low on ' + @@SERVERNAME
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @l_Recipients,
@subject = @l_MailSubject,
@body = @l_AlertMessage
END
GO
IF OBJECT_ID('dbo.getAvailableDiskSpaceTrend', 'P') IS NOT NULL
DROP PROCEDURE dbo.getAvailableDiskSpaceTrend
GO
-- Procedure: getAvailableDiskSpaceTrend
-- Purpose: Get available disk space
-- Location: NutsAndBolts
-- Authorized to: SQLAgent
-- Last Update:
-- Parameter:
-- Input
-- 1. p_StartDate SMALLDATETIME - Beginning date
-- Default is 2 months ago
-- 2. p_EndDate SMALLDATETIME - Ending date
-- Default is today
-- Output
-- None
--
-- Result Set:
-- None
--
-- Return: @@ERROR
-- Exception return values: None
--
-- Author: John Panfil
-- Create Date: 11/18/2004
-- Revisions:
--
CREATE PROCEDURE dbo.getAvailableDiskSpaceTrend
@p_StartDate SMALLDATETIME = NULL,
@p_EndDate SMALLDATETIME = NULL
AS
SET NOCOUNT ON
IF @p_StartDate IS NULL
BEGIN
SET @p_StartDate = DATEADD(mm,-2,GETDATE())
END
IF @p_EndDate IS NULL
BEGIN
SET @p_EndDate = GETDATE()
END
DECLARE @l_Recipients VARCHAR(500),
@l_Subject VARCHAR(500),
@l_Message NVARCHAR(MAX)
SELECT @l_Recipients = 'Africa.Zhou@morningstar.com',
@l_Subject = 'Server Free Disk Space Report on ' + @@SERVERNAME
IF EXISTS ( SELECT 1
FROM NutsAndBolts.dbo.AvailableDiskSpace
WHERE LastUpdate BETWEEN @p_StartDate AND @p_EndDate )
BEGIN
SELECT @l_Message = N'<html>' + CHAR(10) +
N'<head>' + CHAR(10) +
N'<style>' + CHAR(10) +
N' body { font-family: tahoma; }' + CHAR(10) +
N' h1 { font-size: 14px }' + CHAR(10) +
N' td { font-size: 11px; height: 23px; background: #ffffff; padding: 4px }' + CHAR(10) +
N' th { font-size: 11px; height: 23px; background: #efefef; padding: 4px; font-weight: 1000; text-align: left }' + CHAR(10) +
N' table { font-size: 11px; background: #bfbfbf }' + CHAR(10) +
N'</style>' + CHAR(10) +
N'</head>' + CHAR(10) +
N'<body>' + CHAR(10) +
N'<h1>Server Free Disk Space Report: ' + CONVERT(VARCHAR(19), @p_StartDate, 120) + ' to ' + CONVERT(VARCHAR(19), @p_EndDate, 120) + '</h1>' + CHAR(10) +
N'<table cellspacing="1">' + CHAR(10) +
N'<tr><th>Server name</th><th>Last update</th><th>Driver</th><th>Free disk space(MB)</th></tr>' +
CAST ( ( SELECT td = @@SERVERNAME, '',
td = LastUpdate, '',
td = DriveLetter, '',
td = FreeMB
FROM NutsAndBolts.dbo.AvailableDiskSpace
WHERE LastUpdate BETWEEN @p_StartDate AND @p_EndDate
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) + CHAR(10) +
N'</table>' + CHAR(10) +
N'<body>' + CHAR(10) +
N'<html>'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @l_Recipients,
@subject = @l_Subject,
@body = @l_Message,
@body_format = 'HTML'
END
GO
-- Drop older version if it exists
IF OBJECT_ID('dbo.getDDLEventLogWeekly', 'P') IS NOT NULL
DROP PROCEDURE dbo.getDDLEventLogWeekly
GO
-- Procedure: getDDLEventLogWeekly
-- Purpose:
-- Location: NutsAndBolts
-- Authorized to:
-- Last Update:
--
-- Parameter:
-- Input
-- None
-- Output
-- None
-- Return:
--
-- Note:
--
-- Author: Michael .H
-- Create date: 2008-03-04
--
-- Revisions:
--
CREATE PROCEDURE dbo.getDDLEventLogWeekly
WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @l_BeginDate SMALLDATETIME,
@l_EndDate SMALLDATETIME,
@l_Recipients VARCHAR(500),
@l_Subject VARCHAR(500),
@l_Message NVARCHAR(MAX),
@l_DateNow SMALLDATETIME
SET @l_DateNow = CONVERT(VARCHAR(10), GETDATE(), 120)
SET DATEFIRST 1
SELECT @l_BeginDate = DATEADD(d, - DATEPART(dw, @l_DateNow) - 7, @l_DateNow),
@l_EndDate = DATEADD(d, - DATEPART(dw, @l_DateNow), @l_DateNow),
@l_Recipients = 'Africa.Zhou@morningstar.com',
@l_Subject = 'DDL Event(s) Weekly Report on ' + @@SERVERNAME
IF EXISTS ( SELECT 1
FROM dbo.EventLog
WHERE EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') >= @l_BeginDate
AND EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') < @l_EndDate )
BEGIN
SELECT @l_Message = N'<html>' + CHAR(10) +
N'<head>' + CHAR(10) +
N'<style>' + CHAR(10) +
N' body { font-family: tahoma; }' + CHAR(10) +
N' h1 { font-size: 14px }' + CHAR(10) +
N' td { font-size: 11px; height: 23px; background: #ffffff; padding: 4px }' + CHAR(10) +
N' th { font-size: 11px; height: 23px; background: #efefef; padding: 4px; font-weight: 1000; text-align: left }' + CHAR(10) +
N' table { font-size: 11px; background: #bfbfbf }' + CHAR(10) +
N'</style>' + CHAR(10) +
N'</head>' + CHAR(10) +
N'<body>' + CHAR(10) +
N'<h1>Database DDL Event Log: ' + CONVERT(VARCHAR(19), @l_BeginDate, 120) + ' to ' + CONVERT(VARCHAR(19), @l_EndDate, 120) + '</h1>' + CHAR(10) +
N'<table cellspacing="1">' + CHAR(10) +
N'<tr><th>Database name</th><th>Event type</th><th>User name</th><th>Login name</th><th>Post time</th><th>Command text</th></tr>' +
CAST ( ( SELECT td = EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)'), '',
td = EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)'), '',
td = EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)'), '',
td = EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)'), '',
td = EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), '',
td = EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
FROM NutsAndBolts.dbo.EventLog
WHERE EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') >= @l_BeginDate
AND EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') < @l_EndDate
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) + CHAR(10) +
N'</table>' + CHAR(10) +
N'<body>' + CHAR(10) +
N'<html>'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @l_Recipients,
@subject = @l_Subject,
@body = @l_Message,
@body_format = 'HTML'
END
GO
USE master
GO
IF OBJECT_ID('dbo.sp_LogError', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_LogError
GO
-- Procedure: sp_LogError
-- Purpose: Add erorr message info to ErrorLog table for late review
-- Location: master
-- Authorized to: public
-- Last Update:
--
-- Parameter:
-- Input
-- 1. p_Err INT,
-- 2. p_Msg VARCHAR(500),
-- 3. p_Id VARCHAR(50),
-- 4. p_ProcName VARCAHR(50),
-- 5. p_DBName VARCAHR(30),
-- Output
-- None
-- Return: 0 : Procedure executed sucessfully
-- Other : Procedure execute failure, the error code (system error code)
--
-- Note:
--
-- TIME, ERR, MSG, ID, PROCNAME, DBName, DBUSER, HOSTNAME, CHECKED
-- TIME: The time When error happens
-- ERR: A user defined error message Number stored in ErrorLog table.
-- Error numbers should be greater than 50000. Ad hoc messages raise
-- an error of 50000. The maximum error number is 2^31 - 1.
-- MSG: An ad hoc message string; it can have as many as 8000 chars.
-- All ad hoc messages have a standard message id of 14000.
-- Here we fix the maximum message length to be 100.
-- ID: Operations and/or parameters that involve with the error
-- PROCNAME: Name of the Procedure that error occurs
-- DBName: The name of the current DB in which the procedure runs
-- DBUSER: User database user name
-- HOSTNAME: The workstation name
-- CHECKED: See if the error has been checked
--
-- Author:
-- Create date: 08/23/99
--
-- Revisions:
--
CREATE PROCEDURE dbo.sp_LogError
@p_Err INT,
@p_Msg VARCHAR(500),
@p_Id VARCHAR(50),
@p_ProcName VARCHAR(50),
@p_DBName VARCHAR(30)
WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @l_Err INT
INSERT INTO NutsAndBolts.dbo.ErrorLog ( Err,
Msg,
Id,
ProcName,
DBName )
VALUES ( @p_Err,
@p_Msg,
@p_Id,
@p_ProcName,
@p_DBName )
SET @l_Err = @@ERROR
RETURN @@ERROR
GO
GRANT EXECUTE ON sp_LogError TO public
GO
IF OBJECT_ID('dbo.sp_LogEvent', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_LogEvent
GO
-- Procedure: sp_LogEvent
-- Purpose: Add ddl event message info to EventLog table for late review
-- Location: master
-- Authorized to: rl_DataUpload
-- Last Update:
--
-- Parameter:
-- Input
-- 1. p_EventData XML
-- Output
-- None
-- Return:
--
-- Note:
--
-- Author: Michael .H
-- Create date: 2008-03-04
--
-- Revisions:
--
CREATE PROCEDURE dbo.sp_LogEvent
@p_EventData XML
WITH ENCRYPTION
AS
SET NOCOUNT ON
INSERT INTO NutsAndBolts.dbo.EventLog ( EventData )
VALUES ( @p_EventData )
RETURN @@ERROR
GO
GRANT EXECUTE ON sp_LogEvent TO public
GO
IF OBJECT_ID('dbo.sp_TrackProcUsage', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_TrackProcUsage
GO
-- Procedure: sp_TrackProcUsage
-- Purpose: Track the usage of a stored procedure
-- Location: master
-- Authorized to: None
-- Last Update:
-- Parameter:
-- Input
-- 1 : p_TrackType TINYINT - Type or tracking
-- 0 : Track by procedure
-- 1 : Track for all times
-- 2 : Track by day
-- 2 : p_DBName VARCHAR(20) - Database the procedure resides on
-- 3 : p_ProcName VARCHAR(25) - Procedure Name
-- 4 : p_Id VARCHAR(15) - Identifer used in the procedure
-- Output
-- None
-- Result Set:
-- None
--
-- Return: @@ERROR
-- Exception return values:
--
-- Author: Yi Wang
-- Create date: 12/18/99
-- Revisions:
--
CREATE PROCEDURE dbo.sp_TrackProcUsage
@p_TrackType TINYINT,
@p_DBName VARCHAR(20),
@p_ProcName VARCHAR(25),
@p_Id VARCHAR(15)
AS
SET NOCOUNT ON
DECLARE @l_MaxCnt INT,
@l_Cnt INT,
@l_Today DATETIME,
@l_HostName VARCHAR(20),
@l_DBUser VARCHAR(20),
@l_AppName VARCHAR(30)
IF @p_TrackType IS NULL
SET @p_TrackType = 0
SET @p_DBName = RTRIM(LTRIM(@p_DBName))
SET @p_ProcName = RTRIM(LTRIM(@p_ProcName))
SET @p_Id = RTRIM(LTRIM(@p_Id))
SET @l_MaxCnt = 2147000000
SET @l_Today = GETDATE()
IF @p_TrackType = 2
BEGIN
SET @l_Today = CONVERT(DATETIME, CONVERT(CHAR(10), @l_Today, 120))
END
SET @l_HostName = LEFT(RTRIM(LTRIM(HOST_NAME())), 20)
SET @l_DBUser = LEFT(RTRIM(LTRIM(SUSER_SNAME())), 20)
SET @l_AppName = LEFT(RTRIM(LTRIM(APP_NAME())), 30)
IF @p_TrackType IN (1, 2)
BEGIN
IF EXISTS ( SELECT 1
FROM NutsAndBolts.dbo.ProcUsage
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
AND LastAccess = @l_Today
AND AccessCount > @l_MaxCnt )
BEGIN
UPDATE NutsAndBolts.dbo.ProcUsage
SET AccessCount = 0
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
AND LastAccess = @l_Today
AND AccessCount > @l_MaxCnt
END
UPDATE NutsAndBolts.dbo.ProcUsage
SET Id = @p_Id,
AccessCount = AccessCount + 1
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
AND LastAccess = @l_Today
END
ELSE
BEGIN
IF EXISTS ( SELECT 1
FROM NutsAndBolts.dbo.ProcUsage
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
AND AccessCount > @l_MaxCnt )
BEGIN
UPDATE NutsAndBolts.dbo.ProcUsage
SET AccessCount = 0
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
AND AccessCount > @l_MaxCnt
END
UPDATE NutsAndBolts.dbo.ProcUsage
SET Id = @p_Id,
LastAccess = @l_Today,
AccessCount = AccessCount + 1
WHERE TrackType = @p_TrackType
AND DBName = @p_DBName
AND ProcName = @p_ProcName
AND ISNULL(HostName, '') = ISNULL(@l_HostName, '')
AND ISNULL(DBUser, '') = ISNULL(@l_DBUser, '')
AND ISNULL(AppName, '') = ISNULL(@l_AppName, '')
END
SET @l_Cnt = @@ROWCOUNT
IF ISNULL(@l_Cnt, 0) = 0
BEGIN
INSERT INTO NutsAndBolts.dbo.ProcUsage ( TrackType,
DBName,
ProcName,
Id,
LastAccess)
VALUES ( @p_TrackType,
@p_DBName,
@p_ProcName,
@p_Id,
@l_Today )
END
RETURN @@ERROR
GO
--
-- Add the job Track Free disk space
--
USE msdb
GO
BEGIN TRANSACTION
DECLARE @l_JobId BINARY(16),
@l_Err INT
SELECT @l_Err = 0
IF NOT EXISTS ( SELECT 1
FROM msdb.dbo.syscategories
WHERE name = N'[Uncategorized (Local)]' )
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @l_JobId = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Track Free disk space')
IF (@l_JobId IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysjobservers
WHERE (job_id = @l_JobId) AND (server_id <> 0) ))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Track Free disk space'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
BEGIN
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Track Free disk space'
END
SELECT @l_JobId = NULL
END
BEGIN
-- Add the job
EXECUTE @l_Err = msdb.dbo.sp_add_job @job_id = @l_JobId OUTPUT,
@job_name = N'Track Free disk space',
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level = 0
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job steps
EXECUTE @l_Err = msdb.dbo.sp_add_jobstep @job_id = @l_JobId,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXECUTE dbo.getAvailableDiskSpace',
@database_name = N'NutsAndBolts',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
EXECUTE @l_Err = msdb.dbo.sp_update_job @job_id = @l_JobId,
@start_step_id = 1
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @l_Err = msdb.dbo.sp_add_jobschedule @job_id = @l_JobId,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20041119,
@active_start_time = 50000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @l_Err = msdb.dbo.sp_add_jobserver @job_id = @l_JobId,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
--
-- Add the job Mail Free disk space trend
--
BEGIN TRANSACTION
DECLARE @l_JobId BINARY(16),
@l_Err INT
SELECT @l_Err = 0
IF NOT EXISTS ( SELECT 1
FROM msdb.dbo.syscategories
WHERE name = N'[Uncategorized (Local)]' )
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @l_JobId = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Mail Free disk space trend')
IF (@l_JobId IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysjobservers
WHERE (job_id = @l_JobId) AND (server_id <> 0) ))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Mail Free disk space trend'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
BEGIN
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Mail Free disk space trend'
END
SELECT @l_JobId = NULL
END
BEGIN
-- Add the job
EXECUTE @l_Err = msdb.dbo.sp_add_job @job_id = @l_JobId OUTPUT,
@job_name = N'Mail Free disk space trend',
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level = 0
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job steps
EXECUTE @l_Err = msdb.dbo.sp_add_jobstep @job_id = @l_JobId,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXECUTE NutsAndBolts.dbo.getAvailableDiskSpaceTrend',
@database_name = N'NutsAndBolts',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
EXECUTE @l_Err = msdb.dbo.sp_update_job @job_id = @l_JobId,
@start_step_id = 1
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @l_Err = msdb.dbo.sp_add_jobschedule @job_id = @l_JobId,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 32,
@active_start_date = 20041119,
@active_start_time = 60000,
@freq_interval = 2,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 1,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @l_Err = msdb.dbo.sp_add_jobserver @job_id = @l_JobId,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
BEGIN TRANSACTION
DECLARE @l_JobId BINARY(16),
@l_Err INT
SELECT @l_Err = 0
IF NOT EXISTS ( SELECT 1
FROM msdb.dbo.syscategories
WHERE name = N'[Uncategorized (Local)]' )
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
SELECT @l_JobId = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DDL event weekly report')
IF (@l_JobId IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysjobservers
WHERE (job_id = @l_JobId) AND (server_id <> 0) ))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DDL event weekly report'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
BEGIN
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'DDL event weekly report'
END
SELECT @l_JobId = NULL
END
BEGIN
-- Add the job
EXECUTE @l_Err = msdb.dbo.sp_add_job @job_id = @l_JobId OUTPUT,
@job_name = N'DDL event weekly report',
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level = 0
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job steps
EXECUTE @l_Err = msdb.dbo.sp_add_jobstep @job_id = @l_JobId,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXEC dbo.getDDLEventLogWeekly',
@database_name = N'NutsAndBolts',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
EXECUTE @l_Err = msdb.dbo.sp_update_job @job_id = @l_JobId,
@start_step_id = 1
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @l_Err = msdb.dbo.sp_add_jobschedule @job_id = @l_JobId,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 8,
@freq_interval = 2,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20041119,
@active_end_date = 99991231,
@active_start_time = 500,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @l_Err = msdb.dbo.sp_add_jobserver @job_id = @l_JobId,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
--
-- Add Operators and Alerts
--
USE msdb
GO
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysoperators
WHERE name = N'Africa Zhou' ))
EXECUTE msdb.dbo.sp_delete_operator @name = N'Africa Zhou'
GO
EXECUTE msdb.dbo.sp_add_operator @name = N'Africa Zhou',
@enabled = 1,
@email_address = N'Africa.Zhou@morningstar.com',
@category_name = N'[Uncategorized]',
@weekday_pager_start_time = 80000,
@weekday_pager_end_time = 180000,
@saturday_pager_start_time = 80000,
@saturday_pager_end_time = 180000,
@sunday_pager_start_time = 80000,
@sunday_pager_end_time = 180000,
@pager_days = 62
-- Add alert for full transaction log
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysalerts
WHERE name = N'Full Transaction Log'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Full Transaction Log'
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Full Transaction Log',
@message_id = 9002,
@severity = 0,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Full Transaction Log',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
-- Add notifications for default alerts
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 19 Errors',
@severity = 19,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 19 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 20 Errors',
@severity = 20,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 20 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 21 Errors',
@severity = 21,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 21 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 22 Errors',
@severity = 22,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 22 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 23 Errors',
@severity = 23,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 23 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 24 Errors',
@severity = 24,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 24 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
EXECUTE msdb.dbo.sp_add_alert @name = N'Sev. 25 Errors',
@severity = 25,
@enabled = 1,
@delay_between_responses = 60,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Sev. 25 Errors',
@operator_name = N'Africa Zhou',
@notification_method = 1
GO
--
-- Remove SQL Server built-in accounts
USE master
GO
DROP LOGIN [BUILTIN\Administrators]
GO
PRINT 'SQL Server configuration completed.'
PRINT CHAR(13) + CHAR(10)
PRINT 'Please make sure you do the following:'
PRINT CHAR(13) + CHAR(10)
PRINT ' 1. Change sa password'
PRINT ' 2. Change DTSAgent password'
PRINT ' 3. Install applicable service pack(s)'
PRINT ' 4. Configure Database mail'
PRINT ' 5. Change DMClient & DMEditor password'
PRINT CHAR(13) + CHAR(10)
GO