Auditing requirements have had an impact on SQL Server security. Like with authentication and permissions, auditing requirements have become more stringent and encompassing, especially those in HIPAA, Sarbanes-Oxley and Basel II. Many companies now have a legal obligation to track not only who modified a particular piece of data but also who viewed it.

    Requires Free Membership to View

More on SQL Server
The changing database consolidation landscape

SQL Server database security

Since SQL Server 6 it has been possible to run Profiler to capture most queries and Data Manipulation Language (DML). SQL Server 2005 allows for richer auditing of events; for example, with Data Definition Language (DDL) triggers, you can audit schema changes and use Profiler to audit querying activity to see who views a particular table. However, using Profiler for auditing consumes significant resources.

SQL Server 2008 introduces:

  • Login triggers: These triggers will execute when a user logs on, to enable more granular control over what occurs during a login.
  • Expanded auditing: SQL Server 2008 allows for more granular auditing. For example, you can audit an event such as someone increasing or decreasing inventory above or below a certain event. In addition, SQL Server 2008 allows auditing of any predefined server and database actions. These audits can be written to a file, Windows Application Log or the Windows Server log. SQL Server 2008 also introduces All Actions Audited, a setting that causes all database events to be audited, to comply with the most stringent auditing requirements.
  • Policy-based management: SQL Server 2008 introduces Declared Management Framework (DMF), allowing policies to be created to monitor and report on objects' compliance with specific server, database or table settings. For example, you can increase SQL Server's security posture by using DMF to set a policy that all user tables belong to user schemas and do not belong to the dbo schema. If an object falls out of compliance, a script can be run that makes it complaint or sends out an alert.

About the author

Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

Go to page: 1 - 2 - 3


This was first published in March 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.