You are a database administrator for your company. Your company stores all its product data in a SQL Server2005 database named Products. You upgrade the Products database to SQL Server 2008. After the upgrade,you create the respective logins and users in the new database on SQL Server 2008. Eric, a user in your database, complains that since the upgrade he is not able to access the tables created byanother user, Adam, through a stored procedure. However, Adam is able to access the tables through the storedprocedure.
You need to ensure that Eric is able to access the data while minimizing excessive permissions. What should you do?
A.
Grant the CREATE TABLE permission to Eric so that he can create new tables.
B.
Ask Adam to re-create the stored procedure with the RECOMPILE option.
C.
Grant the db_owner role to Eric.
D.
Ask Adam to re-create the stored procedure with the EXECUTE AS OWNER clause.
Explanation:
You should ask Adam to re-create the stored procedure with the EXECUTE AS OWNER clause. When youupgrade a database to a new version, all the database objects are transferred to the new database. The databaselogins and users are not transferred, and you should explicitly create the database logins and users as theyexisted on the old server. You must also grant the required permissions to users in the new database who accessobjects owned by other users. In this scenario, Eric is trying to access tables through a stored procedure. Adam isable to execute the store procedure and access the data. You could either have Eric run the store procedure asAdam, give Eric permissions to the stored procedure, or edit the procedure to run under the owner’s securitycontext. You should not grant the CREATE TABLE permission to Eric so that he can create new tables. Granting the CREATE TABLE
permission to Eric will allow Eric to create new tables but will not give him permissions to theexisting tables that contain data. You should not ask Adam to re-create the stored procedure with the RECOMPILE option. Re-creating the storedprocedure with the RECOMPILE option will not give Eric permissions to the stored procedure or the tables.Including the RECOMPILE option will cause the stored procedure to be recompiled each time it is run. You should not grant the db_owner role to Eric because granting the db_owner fixed database role to Eric wouldgrant Eric more permissions than required in this scenario. The db_owner fixed database role allows users toperform all types of configuration and maintenance activities in the database, and is not required in this scenario. While permissions are not required on the EXECUTE statement, keep in mind that users that execute a storedprocedure must also have permissions to execute the underlying statements within the stored procedure, such aspermission to use the INSERT statement. If users do not have permissions to run the underlying statements, theuser can impersonate another user that has the permissions using an EXECUTE AS statement.Objective:
Managing SQL Server SecuritySub-Objective:
Manage database permissions.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online >Database Engine > Technical Reference > Transact-SQLReference > EXECUTE AS (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > EXECUTE (Transact-SQL)