Managing Permissions in SQL Server 2008 R2

Database safety is primarily around managing permissions. Permissions are the security instruments that tie principals (As an example, logins) to object (As an example, tables). With SQL Server 2008, authorizations can be applied at a granulated level that delivers a great deal of elasticity and control. Permissions in SQL Server 2008 revolve about three commands: GRANT, REVOKE, and DENY. These three commands remained also used in SQL Server 2005 and SQL Server 2000. When permission is granted, the user or role is given permission to execute an action, such as creating a table. The DENY statement denies permission on an object and avoids the principal from gaining GRANT permission based on association in a group or role. The REVOKE statement eliminates a permission that was previously granted or denied. When stipulating permissions, you need to prudently consider the hierarchy that exists among GRANT, REVOKE, and DENY. This is mainly important when the principal (As an example, user or login) is part of a collection or role and permissions have been granted on objects at diverse scopes of the security model. Following are some examples of the priority that exists between these statements:

A GRANT of a permission removes any REVOKE or DENY on an object. As an example, if a table has SELECT permission denied on it and then the SELECT permission is granted, the DENY permission is then removed on that table.
DENY and REVOKE remove any GRANT permission on an object.
REVOKE removes any GRANT or DENY permission on an object.
Permissions denied at a higher-scope in the security model supersede grants on that permission at a lower scope. Keep in notice that the security model has the server scope at the highest level, shadowed by database and schema. So, if INSERT permission is denied on tables at the database level, and INSERT on a precise table in that database is granted at the schema level, the result is that INSERT is denied on all tables. In this example, a database-level DENY supersedes any GRANT at the lower schema level.
Permissions granted at a upper scope in the security model are overridden by a DENY permission at a lower level. As an example, if INSERT permission is granted on all tables at the database scope, and INSERT is denied on a specific table in the database (schema scope), INSERT is then denied on that specific table.

The consignment of a permission includes the GRANT, DENY, or REVOKE declarations plus the permission that these statements affect. The number of accessible permissions increased in SQL Server 2005 and has been carried onward to SQL Server 2008. Accustomed permissions such as EXECUTE, INSERT, and SELECT that were accessible in SQL Server 2000 are still around, plus the new permissions that were added in SQL Server 2005. Subsequent are some of the new types that were added in SQL Server 2005:

CONTROL—This type confers all defined permissions on the object. This ownership-like capability also flows to any lower-level objects in the security ladder.
ALTER—This type advises the capability to change the object's properties but does not include the ability to make ownership changes. If ALTER is applied on a scope such as a database or a schema, the ability to use ALTER, CREATE, or DROP on any object in the scope is allotted as well.
IMPERSONATE—Thi s type permits the principal to mimic another user or login.
VIEW DEFINITION—Thi s type permits access to SQL Server metadata. This type of data is not granted by default in SQL Server 2008; therefore, the VIEW DEFINITION permission was added to manage access.

You can also view the accessible permissions by using system functions and catalog views. The subsequent example uses the sys .fn_builtin_permissions function to rescue a partial listing of all the available permissions:

SELECT top 5 class_desc, permission_name, parent_class_desc
FROM sys.fn_builtin_permissions(default)
order by 1,2
/* Results from previous query
class_desc permission_name parent_class_desc
APPLICATION ROLE
APPLICATION ROLE
APPLICATION ROLE
ASSEMBLY
ASSEMBLY
*/
ALTER DATABASE
CONTROL DATABASE
VIEW DEFINITION DATABASE
ALTER DATABASE
CONTROL DATABASE

The granularity with which permissions can be applied with SQL Server 2008 is inspiring and, to some degree, exciting. When you look at all the available permissions, you will see that some preparation is needed to manage them. In the past, fixed database roles were humble to use but in many cases provided permissions that went outside what the user needed. MS has supplied the tools to facilitate the concept of "least privileges," which means providing only the privileges that are needed and nothing more.