Security and Permissions
Tracing can expose a lot of information about not only the state of the server, but also the data sent to and returned from the database engine by users. The ability to monitor individual queries down to the batch or even query plan level is at once both powerful and worrisome; even exposure of stored procedure input arguments can give an attacker a lot of information about the data in your database.
In order to protect SQL Trace from users that should not be able to view the data it exposes, previous versions of SQL Server allowed only administrative users (members of the sysadmin fixed server role) access to start traces. That restriction proved a bit too inflexible for many development teams, and as a result it has been loosened.
ALTER TRACE Permission
In SQL Server 2005, a new permission exists, called ALTER TRACE. This is a server-level permission (granted to a login principal), and allows access to start, stop, or modify a trace, in addition to being able to generate user-defined events (more on this later in the chapter, in the “Stored Procedure Debugging” section).
Keep in mind that this permission is granted at the server level, and access is at the server level; if a user can start a trace, he or she can retrieve event data no matter what database the event was generated in. The inclusion of this permission in SQL Server is a great step in the right direction for situations in which developers might need to run traces on production systems in order to debug application issues, but it’s important to not grant this permission too lightly. It’s still a potential security threat, even if it’s not nearly as severe as giving someone full sysadmin access.
To grant ALTER TRACE permission to a login, use the GRANT statement as follows (in this example, the permission is granted to a server principal called “Jane”):
GRANT ALTER TRACE TO Jane;
Protecting Sensitive Event Data
In addition to being locked down so that only certain users can use SQL Trace, the tracing engine itself has a couple of built-in security features to keep unwanted eyes—including those with access to trace—off of private information. SQL Trace will automatically omit data if an event contains a call to a password-related stored procedure or statement. For example, a call to CREATE LOGIN including the WITH PASSWORD option will be blanked out by SQL Trace.
Note In previous versions of SQL Server, SQL Trace automatically blanked out a query event if the string sp_password was found anywhere in the text of the query. This feature has been removed in SQL Server 2005, and you should not depend on it to protect your intellectual capital.
Another security feature of SQL Trace is knowledge of encrypted modules. SQL Trace will not return statement text or query plans generated within an encrypted stored procedure, user-defined function, or view. Again, this helps to safeguard especially sensitive data even from users who should have access to see traces.
© Microsoft. All Rights Reserved.