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.
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 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.
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?