You are a database administrator for your company. You implement application roles to manage the security ofthe applications accessed by your company’s users.
You create an application role with the following code:
CREATE APPLICATION ROLE Cust_role1
WITH PASSWORD = ‘1254mtnl’,
DEFAULT_SCHEMA = Cust;
GO
You have granted all the required permissions to the Cust_role1 role to execute the application. You also grantpermissions on this role to the user Amy who is using the application. When Amy logs in to the application, which system stored procedure is executed by the application?

A.
sp_setapprole

B.
sp_addapprole

C.
sp_unsetapprole

D.
sp_addrolemember

Explanation:

The sp_setapprole system stored procedure is executed by the application when Amy logs in to the application.When a user logs in to an application, the application connects to the SQL Server instance by using thecredentials of the user. Then, the application executes the sp_setapprole system stored procedure by providingthe password specified while creating the application role. The password is known only to the application. Afterthe application role and password are authenticated, the application role is activated, and the connection resumeswith the permissions of the application role. An application role is a role that enables you to restrict data access tousers who log in through a particular application. The
sp_addapprole system stored procedure is not executed by an application when a user logs in to theapplication. The sp_addapprole system stored procedure adds an application role to the database. The sp_unsetapprole system stored procedure is not executed by an application when a user logs in to theapplication. The sp_unsetapprole system stored procedure reverts to the user’s permissions. The sp_addrolemember system stored procedure is not executed by an application when a user logs in to the application. The
sp_addrolemember system stored procedure adds database users, database roles, andWindows logins to an existing database role.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage users and database roles.

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 > System Stored Procedures (Transact-SQL) > Security Stored Procedures (Transact-SQL) >sp_setapprole (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > Identity andAccess Control > Principals > Application Roles