What action would be best?

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 that contains a
table named ABC_Tbl. ABC_DB also contains a stored procedure named ABC_Prod that uses a
sp_executesql Transact-SQL statement to get data from ABC_Tbl.
Everything is going fine. Employees are using ABC_Prod as user ABC_User and it works fine.
Some moths later a new ABC.com security policy is implemented. This security policy does not
allow users to access tables directly in any database. Now the employees are complaining. When
they use ABC_prod they get an error saying: The SELECT permission was denied on the object ‘
ABC_Tbl’, database ‘ ABC_DB ‘, schema ‘dbo’.
What action would be best?

A.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS USER =’dbo’
before the sp_executesql Transact-SQL statement.

B.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS USER
=’administrator’ before the sp_executesql Transact-SQL statement.

C.
You should consider running, GRANT SELECT ON dbo.Master’ TO ABC_User.

D.
You should consider running, GRANT SELECT ON dbo.ABC_Tbl’ TO ABC_User.

E.
Give ABC_User full Administrative permissions.¨

F.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS OWNER option
to its header.

G.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS CLAUSE
option to its header.

H.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS
ADMINISTRATOR option to its header.

I.
You should consider modifying ABC_Prod and change the WITH EXECUTE AS CALLER option
to its header.

J.
Set up appropriate roles and permissions.

Explanation:

EXECUTE AS OWNER is a great way to limit the permissions of a SQL Server Login. The general
idea is to create your stored procedure with the EXECUTE AS OWNER modifier. Any user who
has the permissions to execute the stored procedure, runs the stored procedure under the
Database’s dbo user (which means it can do anything in the database, but nothing at the serverlevel nor on other databases). If you only allow your Logins to execute stored procedures (and not
touch the tables directly), then you’ve effectively limited the Logins to code you’ve written. If you
don’t write any DELETE statements, then Logins can’t delete anything.
This is better than Roles, because Roles are very coarse in comparison. With Roles, you may
have to give a User INSERT permissions on table. Instead with EXECUTE AS OWNER you can

write a stored procedure that checks the data exactly the way you want in the body of the stored
procedure. This is much more fine grained way of handling permissions.