You are the database administrator of your company. The network contains an instance of SQL Server 2008 thatis accessed by all users on the network. The company’s policy states that users must not have direct permission to create database triggers on the SQLserver. Any user that needs to create triggers must use the execution context of a different user or login that hasthe appropriate permissions. To achieve this, you create a new login that has appropriate permissions to createtriggers.
You want to ensure that users are able to use this login with the EXECUTE AS statement.
What should you do?
A.
Ensure that the new login exists in the sys.database_principals view.
B.
Ensure that the new login exists in the sys.server_principals view.
C.
Ensure that the new login exists in the sys.database_role_members view.
D.
Ensure that the new login exists in the sys.server_role_members view.
Explanation:
You should ensure that the new login exists in the sys.server_principals view. The EXECUTE AS statementdefines the execution context for a user’s session. During a session when a user performs any operation, SQLServer checks the user’s permission to determine whether the user has the required permission to perform thatoperation. If the user does not have the required permission, the operation is not performed. When a userperforms an operation by using the EXECUTE AS statement, the permissions are checked against the user orlogin name specified in the EXECUTE AS statement. The EXECUTE AS statement runs successfully only if theuser or login name specified in the statement exists as a principal in the sys.database_principals or sys.server_principals view. If the user needs to execute the statement across the entire server, the principal islocated in the sys.server_principals view. If the user needs to execute the statement only within a singledatabase, the principal is located in the sys.database_principals view. You should not ensure that the new login name exists in the
sys.database_principals view. The sys.database_principals view contains information about each principal in a database. This answer would havebeen correct if you had used a database user name in the EXECUTE AS statement. In this scenario, you havecreated a new SQL login, which is a server-level object. Therefore, it must exist in the sys.server_principals view because this view contains information about every server-level principal. You should not ensure that the new login name exists in the sys.database_role_members view or in the sys.server_role_members view. The EXECUTE AS statement runs successfully only if the user or login namespecified in the statement exists as a principal in the sys.database_principals
or sys.server_principals view.The sys.database_role_members view contains information about each member of each database role. The sys.server_role_members view contains information about each member of each fixed server role.Objective:
Managing SQL Server SecuritySub-Objective:
Manage SQL Server instance 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)