Flexible Database Level Roles
Flexible database roles enable us to use roles with a custom set of permissions. We can nest flexible database roles in a manner similar to the way we nest security groups in Active Directory. These database roles simplify the management of permissions. I would never grant access on a user by user basis, I would create a group an then add the user to that group or securable.
We can create flexible database-level roles and assign custom permissions to these roles so we can be more specific with the assignment of permissions rather than using the more general fixed database-level roles.
The CREATE ROLE statement is used to create flexible database roles
The ALTER ROLE statement enables you to change the name of a flexible database role not the membership
sp_addrolemember stored procedure is used to add members to a flexible database role
After we have created a role we can then create a schema owned by that role.
SQL Server 2012 also includes fixed database roles that are assigned a fixed set of permissions.
Schemas simplify the application of permissions by enabling you to collect securables into containers.
Objects belong to schemas, and schemas belong to security principals.
Determining Effective Permissions
You can use the HAS_PERMS_BY_NAME function to determine a specific principal’s effective permission on a certain securable. An effective permission can be one of the following:
- Permission is granted directly to the security principal
- Permission is granted to a role of which the security principal is a member
- Permission is implied by a higher-level permission held by the security principal
- Permission is not denied to the principal directly or indirectly through role membership
- An application role is a special role used by an application to access a database
- Application roles are secured by passwords