This chapter is from the book
This chapter is from the book
This chapter is from the book
Removing Unwanted Data
Over time, data becomes obsolete and needs to be removed. You remove data by using the DELETE statement. It is extremely important to provide a condition when performing data deletions because it is easy to remove all data if you do not exercise caution. The following command removes all records from the specified table:
DELETE FROM Customers
If you would like to delete all rows, you can use the fast, nonlogged method TRUNCATE TABLE. This immediately frees all space used by indexes and data by that table. On the other hand, DELETE should be used when partial data removal is desired. Although both TRUNCATE TABLE and a DELETE statement with no WHERE clause remove all rows in a table, TRUNCATE TABLE is faster and uses fewer system and log resources. The DELETE statement removes rows one at a time, recording an entry in the transaction log for each row.
TRUNCATE TABLE removes data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, and indexes remain intact. The counter used by any identity columns is reset to the seed value for the column. If you want to retain the identity counter, you should use DELETE instead.
Directly Removing Records from a Table
The DELETE statement removes one or more records from a table, based on a condition in the WHERE clause. The following is a simplified sample DELETE statement:
DELETE FROM Products WHERE Discontinued = 1
In a fashion similar to that used to remove a single record via a positioned update, you could also create a procedure that performs a positioned deletion by using the DELETE statement with the WHERE CURRENT OF clause as part of a CURSOR operation.
Indirectly Removing Data from a Table
Removing data from a table can affect other tables if cascading deletions have been specified for a relationship. This could mean the removal of other records based on a single DELETE operation. Also, a relationship definition may prevent the deletion of data and return an error condition to the operation. Triggers, restore operations, and stored procedures are other processes that could indirectly delete records.
Escalating Privileges to Allow Deletion
You often need to have an application remove data from a database on the user's behalf. In cases where users with limited permissions need to delete data in a special circumstance, it is best to do so through a controlled and logged procedure.
You could create a stored procedure to perform the deletion and optionally log information about who is performing the task, when, and why the deletion occurred. If the user does not have the rights to delete records, the procedure could be executed under a user ID or role that has that permission.
Normally, during a session, permission checks are performed based on the user currently logged in. When an EXECUTE AS statement is run, the context of the session changes. This change lasts for the duration of the immediate scope. After the switch, permissions are checked against the login used in the EXECUTE AS operation. In essence, the user or login account is impersonated for the duration of the batch or module execution. This allows operations to be performed that the user context would not normally allow.
IMPERSONATE permissions must be granted to the individual running the process with the EXECUTE AS operation. If the user logged into the system is the database owner or a member of the sysadmin role, these permissions are inherited. In this respect, the owner or administrator can test processes with a reduced level of permission to ensure that security measures that are in place are functioning as expected.
Controlling Privileges by Using GRANT, DENY, and REVOKE
Permissions on objects in a database can be assigned to users and roles either directly, using the graphical tools, or programmatically, through the use of scripting. Groups and logins can be associated with users and roles and thus have permissions affect them within the database. Permissions can also be assigned directly to logins within the server that affect the systemwide functions that can be performed.
The T-SQL GRANT, DENY, REVOKE statements can be executed within a procedure to deal with permissions through process execution. DENY is the strongest of the three permissions; when assigned, it takes precedence over all other permissions. Therefore, you need to be careful never to deny permission to a resource for the public role because doing so effectively blocks everyone from using the resource.