You are a database administrator for AIOTestKing.com. One of the databases on a SQL Server 2005 computer contains a stored procedure. Users run this stored procedure to import data into a table. The stored procedure needs to use the TRUNCATE TABLE command before importing new data into the table. However, the users who run the stored procedure do not have permission to truncate the table. You need to provide a way for the stored procedure to truncate the table before it imports new data. What should you do?
A.
Configure the stored procedure to use the EXECUTE AS command.
B.
Configure the stored procedure to be owned by the same database user as the table.
C.
Assign the users DELETE permission in the table.
D.
Add the users to the db_datawriter fixed database role.
Explanation:
In SQL Server 2005 you can implicitly define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the SQL Server 2005 Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.
Syntax:
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ }