You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. The SQL Server 2008 instance contains a database named ABC_DB.You add a stored
procedure named RecTable to the database using the following code (NOTE: Line numbers are
included for reference only):
01 CREATE PROCEDURE Prods.RecTable
02
03 AS
04
05 IF OBJECT_ID(‘Prods.ManuTeam’) IS NOT NULL
06 DROP TABLE Prods.ManuTeam;
07
08 CREATE TABLE Prods.ManuTeam(
09ManuIdint PRIMARY KEY CLUSTERED,
10TeamSizesmallint,
11 TeamLeadernvarchar(150);
12 );
13
14 GO
15
However, when a ABC.com usernamed Mia Hamm tests the RecTable stored procedure she
receive an error stating CREATE TABLE permission denied in ABC_DB.
How would you rectify this problem without giving Mia Hamm more permissions than she
requires?
A.
You should make Mia Hamm’s SQL Server login a member of the db_owner role.
B.
You should consider assigning the CREATE TABLE permission to Mia Hamm’s SQL Server
login.
C.
You should alter the stored procedure to include the code WITH EXECUTE AS ‘dbo’ at line 02.
D.
You should consider changing line 01 toCREATE PROCEDURE dbo.RecTable.
Explanation: