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.
|
1 2 3 |
CREATE APPLICATION ROLE APPROLETest WITH PASSWORD = '123456' , DEFAULT_SCHEMA = dbo; |
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.
|
1 |
GRANT SELECT ON SCHEMA::dbo TO APPROLETest; |
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.
|
1 |
EXEC sp_setapprole 'APPROLETest', '123456'; |
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.
|
1 2 3 |
USE TEST EXEC sp_setapprole 'APPROLETest', '123456'; SELECT * FROM [Test].[dbo].[MyTable] |
