What is the difference between creating database role and application role?

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 :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 CONNECT permission.

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply