Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 8, 2000

Automatic Securing of SQL Server Configuration Settings

By Jarlath O'Grady

[SQL Server Version 6.5]

Through experience I have found it necessary to maintain an up-to-date copy of the various SQL Server settings. It is also important to hold this information separately from the live server in case of complete server failure.

The following method is one that I have implemented successfully using three stored procedures and a scheduled task. This task dumps the following SQL Server information to a the text file Recovery.out on a floppy disk along with SQL scripts to create all existing databases.

  • Date of Generation
  • Server Name and User Name
  • Current SQL Server Version
  • Current Sort Order & Character Set
  • Current Standard & Advanced Configuration Options
  • Current Database Structure
  • Current Device Structure

In order to execute the commands contained within the stored procedures, the SA login is used, but this exposes the SA password. In order to hide the password, it is stored within a task, created by the SA. Since only the SA can examine SA created tasks through the 'Manage Scheduled Tasks' window the password cannot be seen by users. This does not fully protect the password though, because the DBO of database MSDB can examine the system table SYSTASKS, which contains the task information (see table below).
User SA Task SYSTASKS Table
User Access to MSDB Objects

The 'Recovery Disk' task is created through the execution of the stored procedure,  sp_recoverytask. This task will contain the SA password within the command, but this will only be visible by SA and MSDB DBO. The SA password [VarChar (30)] is passed to sp_recoverytask as a variable, this ensures that the password is not hard coded. Along with the password, the name for the task and an indicator are passed to the stored procedure.

The task name [VarChar (100)] is the name that the task will be referred to, and the overwrite indicator [TinyInt] determines if an existing task of the same name, will be overwritten. By default this is set to 0 and the task will not be overwritten. To overwrite set to 1.

EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
This EXECUTE command will create a task Recovery Disk, scheduled to run at 12:00 am each day, with the command:
EXECUTE sp_recoverydisk "SQLServer1"
This task, Recovery Disk,  will execute sp_recoverydisk, which in turn executes sp_structurescript to produce file recovery.out, and sp_help revdatabase to produce script files for each database (e.g. master.sql and mydb.sql) and one script file for all databases (alldbs.sql).


Creation of stored procedures in master database:

  1. Download the file sqlsecur.sql (8kB)
  2. Open file sqlsecur.sql in SQL Query tool, with master database selected under SA login
  3. Execute the script

Creation of a task to execute the stored procedure:

  1. Execute procedure, sp_recoverytask, with parameters Task Name, SA Password and Overwrite Indicator:
  2. EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
  3. Confirm the task works correctly by manually running it, to create the files on floppy disk
  4. Confirm the output within the file is correct and contains no errors (e.g. login failures)


The floppy disk can now be left permanently in the drive of the server, though I recommend changing the boot options of the machine so as to boot from the hard drive first, to ignore the non-bootable disk.

The scheduled time of the task can be modified.

Altough the SA password is been held within table msdb..systasks, and only visible by SA and DBO, SELECT permissions on this table should be restricted.

Comments welcome.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM