You administer a SQL Server 2008 instance that contains a database named DB1. DB1
contains a table named Table1.
The DB1 database includes a stored procedure named Procedure1. Procedure 1 uses a
sp_executesql Transact-SQL statement to select data from Table1.
According to business requirements, users are not allowed to access tables directly in any
database.
When a user executes Procedure1, the following exception is raised:
“Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object Table1′, database ‘DB1’, schema ‘dbo’.”
You need to ensure that the user can successfully execute Procedure1 without violating the
business requirements.
What should you do?
A.
Execute the GRANT SELECT ON dbo.Table1 TO User1 Transact-SQL statement.
B.
Execute the GRANT EXECUTE ON dbo.Procedure1 TO User1 Transact-SQL statement.
C.
Alter Procedure1 and add the WITH EXECUTE AS OWNER option to its header.
D.
Alter Procedure1 and add the EXECUTE AS USER = ‘dbo’ option immediately before the
call to the sp_executesql stored procedure.