What is Application Role in SQL Server

 

Application Role is a role at the database level. You can use Application Roles to access a database only with specific users from a specific application.

These roles are not active by default. You can create an application role on the database in the following way.

Because Application Roles are defined on a database basis, they cannot access any data in the instance level. You can authorize an existing application role on a schema basis as follows.

Application Roles do not have members, such as database roles. You can activate application roles from the application using the sp_setapprole stored procedure as follows.

Because Application Roles are not connected to any login, moving the database to another instance will be easier.

We can create user without login since SQL Server 2005 version. This feature was introduced instead of Application Role. You may want to read my article “How To Create SQL User Without Login”

Another way to connect to a database without a login is to convert the database to a Contained Database. You may want to read my article “What is Contained Database“.

You can create a user with only public authority and run the Select query with the following script.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *