Step 12. SQL Server Database Objects

SQL Server provides two sample databases for development and education together with a series of built-in stored procedures and extended stored procedures. The sample databases should not be installed on production servers and powerful stored procedures and extended stored procedures should be secured.

In this step, you:

• Remove the sample databases .

• Secure stored procedures .

• Secure extended stored procedures .

• Restrict cmdExec access to the sysadmin role .

Remove the Sample Databases

Use SQL Server Enterprise Manager to remove any sample databases. By default SQL Server includes the Pubs and Northwind sample databases.

Secure Stored Procedures

Restrict access to your application's stored procedures. Do not grant the public role or the guest user access to any stored procedures that you create. Your main line of defense for securing stored procedures is to ensure that you use strong authentication, and then to provide granular authorization, allowing only the necessary users permission to run the stored procedures.

The recommended approach is to create a SQL Server login for your application, map the login to a database user, add the user to a user-defined database role, and then grant permissions to the role.

Secure Extended Stored Procedures

Deleting stored procedures is not tested and not supported.

Restrict cmdExec Access to the sysadmin Role

The cmdExec function is used by the SQL Server Agent to execute Windows command-line applications and scripts that are scheduled by the SQL Server Agent. Prior to SQL Server Service Pack 3, by default the SQL Server Agent allows users who are not in the sysadmin role to schedule jobs that may require privileged access to the system. You should change this setting to allow members only of the sysadmin role to schedule jobs.

 Task   To restrict cmdExec access to the sysadmin role

1. Start SQL Server Enterprise Manager , expand the SQL Server Group , and then expand your SQL Server .

2. Expand the Management node , right-click SQL Server Agent, and then click Properties .

   The SQL Server Agent Properties dialog box is displayed.

3. Click the Job System tab.

4. At the bottom of the dialog, select the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box.

5. Click OK .

   Note  

   This change may require you to supply a user name and password. If the SQL Server service account is least privileged user (as advocated earlier in this chapter), you will be prompted for the user name and password of an administrator account that has privileges to modify the service.