SQL 2008 R2: Auditing and moving system databases (Part 3)

Welcome to Part 3 of my SQL 2008 R2 installation and lockdown series. The script below must be run in SQL Studio and is step #2 in my series introduction. It turns on SQL auditing and pipes it to the Windows security event log, renames the SA account, and then points the SQL system databases and log files to a custom location. Note this doesn’t actually MOVE the databases and log files, so that’s where my PowerShell script in the final step comes into play.

—–
/* Execute within SQL Studio */
/* Lockdown queries for SQL Server 2008/R2 Version 1.4, 3 July 2010 */
/* Configure Auditing */

/* Create a SQL Server Audit Object that writes the audit results to the Windows Security Log every one second. If the write fails, the instance continues running without stopping. */

CREATE SERVER AUDIT SQL_Audit
TO SECURITY_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

/* Create a Server Audit Specification object for the server audit. This object include three audit action groups related to server principal changes. */

CREATE SERVER AUDIT SPECIFICATION SQL_Audit_Spec
FOR SERVER AUDIT SQL_Audit
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);

/* By default, both the audit and audit specification are created in the disabled state. We need to enable them before using them to record actions. */

ALTER SERVER AUDIT SPECIFICATION SQL_Audit_Spec
WITH (STATE = ON);
ALTER SERVER AUDIT SQL_Audit
WITH (STATE = ON);

/* Rename SA Account */

ALTER LOGIN sa WITH NAME = [Hollywood];

/* Move system databases */

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = ‘K:Microsoft SQL ServerMSSQLDataModel.mdf‘)

ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = ‘L:Microsoft SQL ServerMSSQLDataLogsModellog.ldf‘)

ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = ‘K:Microsoft SQL ServerMSSQLDatamsdbdata.mdf‘)

ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog, FILENAME = ‘L:Microsoft SQL ServerMSSQLDataLogsmsdblog.ldf‘)

Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments