Article
10/04/2012

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
	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).
	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.
	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.
	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).
	Have you replaced remote servers with linked servers? Tip For more information, see Configuring Remote Servers and Linking Servers.
	If pass-through authentication to a linked server is necessary have you constrained delegation? Tip For more information, see sp_addlinkedsrvlogin (Transact-SQL).
	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
	Is the guest user account disabled in every database unless required for anonymous users? Revoke the guest user permission to access the database if it is not required. The guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission by executing REVOKE CONNECT FROM GUEST within any database other than master, tempdb, or msdb. Tip Disable accounts using SQL Server Management Studio or Transact-SQL.
	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.
	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.
	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).
	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).
	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.
	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.
	Are modules signed to inhibit tampering? Tip For more information, see Module Signing (Database Engine).
	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.
	Do you use EXECUTE AS instead of SETUSER? Tip For more information, see EXECUTE AS vs. SETUSER.
	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
	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.
	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).
	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).
	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.
	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).
	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.
	Do modules take steps to prevent SQL Injection? Tip: For more information, see SQL Injection.
	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. How To: Protect From SQL Injection in ASP.NET Microsoft Source Code Analyzer for SQL Injection The Microsoft Source Code Analyzer for SQL Injection tool is available to find SQL injection vulnerabilities in ASP code New SQL Truncation Attacks And How To Avoid Them Stop SQL Injection Attacks Before They Stop You

Auditing Access

...	Description
	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.
	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.
	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.
	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.
	Do you use WMI to be alerted of emergency events? Tip For more information, see WMI Provider for Server Events Concepts.
	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.

Checklist: Limiting Access to Data

Access to the Instance of SQL Server

Managing User Identity

Object Access

Auditing Access

See Also

Concepts

Other Resources

Additional resources