Application roles are DATABASE level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we PROVIDE a password to unlock access to the database.

Here it is how it works:

• Create a login on SQL SERVER for application user
• Create an application role on the corresponding database.
• Give the application role password to the user
• User will have access to login to SQL Server but doesn’t have any access to the database INCLUDING public role.
• He/she just need to provide the password to unlock the access to the database
• EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
• Once it is executed successfully the user will get all rights that your app role have on that database.

Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database.

Here it is how it works: