All we need is an easy explanation of the problem, so here it is.
I want to have a role called ValidUsers.
My application will check if the user belongs to this group. If so then allow him to access the application.
Now when I right click roles under the database, I can see database role and application role. What is the difference between these and which fits into my use case?
How to solve :
Application Roles are a mechanism where you allow a client application to connect the user via their own login (either Windows Authentication or SQL Server Authentication) – once the user has connected the application executes
sp_setapprole to switch security context into the app role.
The app role would have appropriate permissions as needed in the database. The user’s login would only have
This prevents connections from seeing data in the database unless they know the password for the app role.
Take a look at the Microsoft Docs for more details and examples.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂