Checklist: Limiting Access to Data

This checklist helps you review how you limit access to data in your organization. Use this checklist to periodically audit how users access information stored in the SQL Server Database Engine.

Access to the Instance of SQL Server

These items relate to the entire instance of the Database Engine.

...

Description

Boolean Field Icon
Have you granted access through Windows groups for most logins ?

Tip Configuring access to the Database Engine by using Windows groups makes access easier to administer and maintain. For more information about logins, see Principals (Database Engine).

Boolean Field Icon
Have you removed unnecessary or obsolete logins from the Database Engine?

Tip This may require periodic manual review. Enabling access primarily through Windows groups can make this task easier.

Boolean Field Icon
Have you implemented the principle of least privilege?

Tip Principals (logins, users, and roles) should only be granted permissions to those database objects that they must access to accomplish their work. Do not allow routine users to connect using an administrator account, such as sa. Do not allow your web page, custom application, or SSIS package to connect using an administrator account.

Boolean Field Icon
To view system metadata without conferring additional permissions, have you granted the VIEW DEFINITION permission selectively at the object, schema, database, or server level?

Tip For more information, see GRANT (Transact-SQL).

Boolean Field Icon
Have you replaced remote servers with linked servers?

Tip For more information, see Configuring Remote Servers and Linking Servers.

Boolean Field Icon
If pass-through authentication to a linked server is necessary have you constrained delegation?

Tip For more information, see sp_addlinkedsrvlogin (Transact-SQL).

Boolean Field Icon
Have you disabled ad hoc queries through servers (unless needed)?

Tip For more information, see ad hoc distributed queries Option.

Managing User Identity

These items relate to settings on each database.

...

Description

Boolean Field Icon
Is the guest user account disabled in every database unless required for anonymous users?

Tip Disable accounts using SQL Server Management Studio or Transact-SQL.

Boolean Field Icon
Do users only have access to necessary databases?

Tip This may require periodic manual review. Enabling access primarily through SQL Server roles can make this task easier. For more information, see Server-Level Roles.

Boolean Field Icon
Have most users been granted access through SQL Server roles?

Tip Configuring access using server and database roles makes access easier to maintain. For more information about roles, see Database-Level Roles.

Boolean Field Icon
Does the SQL Server Agent use credentials to execute job steps that require specific privileges rather than adjusting the privileges of the SQL Server Agent service account?

Tip For more information, see Credentials (Database Engine).

Boolean Field Icon
If a SQL Server Agent user needs to execute a job that requires different Windows credentials, have you assigned them a proxy account that has just enough permissions to accomplish the task?

Tip For more information, see How to: Create a Proxy (SQL Server Management Studio).

Boolean Field Icon
Do you encapsulate access to database objects within modules such as stored procedures, functions, triggers, or assemblies?

Tip: Limiting access to predefined modules makes it harder for a malicious user to run arbitrary code. For more information, see Understanding Stored Procedures.

Boolean Field Icon
In modules, have you explicitly set an execution context rather than using the default context?

Tip For more information, see Using EXECUTE AS in Modules.

Boolean Field Icon
Are modules signed to inhibit tampering?

Tip For more information, see Module Signing (Database Engine).

Boolean Field Icon
Do you use USER WITHOUT LOGIN instead of application roles?

Tip For more information, see SQL Server 2005 Security Best Practices - Operational and Administrative Tasks.

Boolean Field Icon
Do you use EXECUTE AS instead of SETUSER?

Tip For more information, see EXECUTE AS vs. SETUSER.

Boolean Field Icon
Have you replaced application roles with EXECUTE AS?

Tip Use EXECUTE AS … WITH NO REVERT when possible. Use the EXECUTE AS … WITH COOKIE option when nesting identity changes. For more information, see EXECUTE AS (Transact-SQL).

Object Access

These items relate to accessing database objects.

...

Description

Boolean Field Icon
Are the public server and database roles granted few (if any) permissions?

Tip All logins and users are members of the public roles and cannot be removed. These roles should have very limited permissions.

Boolean Field Icon
Are similar database objects grouped together into the same schema?

Tip Create schemas based on business requirements. Use these custom schemas instead of the dbo schema. For more information, see Schemas (Database Engine).

Boolean Field Icon
Do you manage database object security by setting ownership and permissions at the schema level?

Tip For more information, see GRANT Schema Permissions (Transact-SQL).

Boolean Field Icon
Do you have distinct owners for schemas instead of having all schemas owned by dbo?

Tip When all schemas have the same owner, ownership chaining may bypass necessary permission checks. For more information, see Ownership Chains.

Boolean Field Icon
Do you use code signing of procedural code if additional privileges are required for the procedure?

Tip For more information, see Module Signing (Database Engine).

Boolean Field Icon
Is the TRUSTWORTHY database option set to OFF?

Tip When set to ON, database modules (such as user-defined functions or stored procedures) that use an impersonation context can access resources outside the database. Use the ALTER DATABASE statement to change the TRUSTWORTHY setting. For more information, see TRUSTWORTHY Database Property.

Boolean Field Icon
Do modules take steps to prevent SQL Injection?

Tip: For more information, see SQL Injection.

Boolean Field Icon
If ad-hoc access to the data base is permitted (instead of encapsulating access within modules), are applications taking measures to prevent SQL Injection?

Tip For more information, see the following links.

Auditing Access

...

Description

Boolean Field Icon
Is auditing scenario-specific?

Tip Balance the need for auditing with the overhead of generating additional data. SQL Server Audit can enable auditing in a single database and for DML to specific objects. For more information, see Understanding SQL Server Audit.

Boolean Field Icon
Is login auditing configured to retain a record of failed logins?

Tip Configure login auditing by using the Server Properties (Security Page) in Management Studio.

Boolean Field Icon
Do you audit both successful logins and unsuccessful logins if you store highly sensitive data?

Tip For more information, see Audit Login Event Class and Audit Login Failed Event Class.

Boolean Field Icon
Do you audit DDL, DML, and specific server events by using SQL Server audit or trace events?

Tip For more information, see Understanding SQL Server Audit, Understanding Event Notifications, DDL Events, and Trace Events for Use with Event Notifications.

Boolean Field Icon
Do you use WMI to be alerted of emergency events?

Tip For more information, see WMI Provider for Server Events Concepts.

Boolean Field Icon
Do you enable C2 auditing or Common Criteria compliance only if required?

Tip Target your configuration to your business needs. Only enable C2 auditing or Common Criteria compliance if appropriate for your business. See Common Criteria Certification.