Join Gerry O'Brien for an in-depth discussion in this video Understanding how SQL Server checks permissions, part of Securing SQL Server 2012.
When it comes to applying permissions in SQL Server, a specific…algorithm is used for checking what are known as Effective Permissions.…Database users may belong to multiple roles that have conflicting permissions.…As a result, SQL Server needs to evaluate the correct permissions for the user.…And then determine whether they have access…to the secureable they're attempting to work with.…The security check algorithm used by SQL is complex.…And the complexity comes from…the need to evaluate group or role…membership, as well as explicit and implicit permissions.…
SQL Server will first ensure that all permissions associated…with a principal and a securable are taken into consideration.…SQL Server permission checking entails three core elements.…The first is the security context.…This is related to the user, the login or the role.…So as an example, as we expand Security, the security context…could apply to the logins.…In this case, the system administrator account or perhaps any…of the Windows accounts that we have existing on the server.…
- Understanding security best practices
- Managing logins and users
- Understanding how SQL Server checks permissions
- Creating and assigning logins and roles
- Securing SQL on the network