Permissions are the individual rights, granted (or denied) to a principal, to access a securable. As in previous SQL Server versions, you can GRANT permissions, DENY permissions, or REVOKE permissions that have already been granted. What’s changed is the sheer number of permissions that you can grant—there are 181 combinations of permissions and securables!
Types of Permissions
The exact permissions that can be granted, and the format for the GRANT and DENY commands, vary according to the securable. These can be broken down into 12 groups:
• Server permissions: Permissions that apply to the server as a whole, such as permission to connect to the server or to an endpoint, permission to create or alter DDL or other events, and permission to access resources external to SQL Server. The controlling permission is CONTROL SERVER, which gives the grantee authority to perform any action and is effectively equivalent to adding the login to the sysadmin fixed server role.
• HTTP endpoint permissions: Permissions to connect to the endpoint and to control, alter, view the definition of, or take ownership of the object.
• Certificate permissions: Permissions to alter or control a specific certificate.
• Database permissions: Database-wide permissions that apply to all objects in the current database. For example, they include permissions to create, alter, and execute objects in the database; to perform select, insert, update, or delete operations in any object in the database; and to control or take ownership of the database.
• Schema permissions: Permissions that apply to a named schema or to all objects within the schema. They include the ability to perform select, insert, update, and delete operations on any object in the schema; to execute any procedure or function in the schema; or to control, alter, or take ownership of the schema.
• Assembly permissions: Permissions on a specific assembly, such as permission to execute, control, alter, or take ownership of the assembly.
• Type permissions: Permissions on a specific user-defined type, such as permission to execute, control, or take ownership of the type.
• Full-text catalog permissions: Permissions to reference, take ownership of, view the definition of, or control the catalog.
• Service Broker permissions: Permissions on a specific Service Broker object. These vary slightly depending on the type of object.
• Server principal permissions: Permissions to impersonate a given login account or to alter, view the definition of, take ownership of, or control the login.
• Database principal permissions: Permissions to impersonate a given user or to alter, control, or view the definition of a specific database principal.
• Object permissions: Permissions granted on a schema-scoped securable such as a table, view, or stored procedure, such as to execute or to perform select, delete, and other operations on the object. You can also specify ALL (or ALL PRIVILEGES) to grant all available permissions on the object.
We can’t cover every permission here, so please consult SQL Server Books Online for a full list of the permissions supported by each type of object.
The basic syntax for the GRANT statement is as follows:
GRANT <permission>
[ON [<securable type>::]<securable>]
TO <principal>
[WITH GRANT OPTION]
[AS {<group> | <role> }]
The ON clause is omitted for database permissions and server permissions, which apply to the current database or server, respectively, as a whole. The <securable type>:: syntax is not used for permissions on database objects such as tables, views, and stored procedures. The WITH GRANT OPTION and AS {<group> | <role> } clauses are optional in all cases. The former gives the grantee the ability to grant the permission in turn to other principals, and the latter indicates the name of a database group or role that the granter belongs to and that has the authority to grant permissions on the securable.
The syntax for DENY and REVOKE follows the same basic format.
Source of Information : Apress Accelerated SQL Server 2008
Types of Permissions
The exact permissions that can be granted, and the format for the GRANT and DENY commands, vary according to the securable. These can be broken down into 12 groups:
• Server permissions: Permissions that apply to the server as a whole, such as permission to connect to the server or to an endpoint, permission to create or alter DDL or other events, and permission to access resources external to SQL Server. The controlling permission is CONTROL SERVER, which gives the grantee authority to perform any action and is effectively equivalent to adding the login to the sysadmin fixed server role.
• HTTP endpoint permissions: Permissions to connect to the endpoint and to control, alter, view the definition of, or take ownership of the object.
• Certificate permissions: Permissions to alter or control a specific certificate.
• Database permissions: Database-wide permissions that apply to all objects in the current database. For example, they include permissions to create, alter, and execute objects in the database; to perform select, insert, update, or delete operations in any object in the database; and to control or take ownership of the database.
• Schema permissions: Permissions that apply to a named schema or to all objects within the schema. They include the ability to perform select, insert, update, and delete operations on any object in the schema; to execute any procedure or function in the schema; or to control, alter, or take ownership of the schema.
• Assembly permissions: Permissions on a specific assembly, such as permission to execute, control, alter, or take ownership of the assembly.
• Type permissions: Permissions on a specific user-defined type, such as permission to execute, control, or take ownership of the type.
• Full-text catalog permissions: Permissions to reference, take ownership of, view the definition of, or control the catalog.
• Service Broker permissions: Permissions on a specific Service Broker object. These vary slightly depending on the type of object.
• Server principal permissions: Permissions to impersonate a given login account or to alter, view the definition of, take ownership of, or control the login.
• Database principal permissions: Permissions to impersonate a given user or to alter, control, or view the definition of a specific database principal.
• Object permissions: Permissions granted on a schema-scoped securable such as a table, view, or stored procedure, such as to execute or to perform select, delete, and other operations on the object. You can also specify ALL (or ALL PRIVILEGES) to grant all available permissions on the object.
We can’t cover every permission here, so please consult SQL Server Books Online for a full list of the permissions supported by each type of object.
The basic syntax for the GRANT statement is as follows:
GRANT <permission>
[ON [<securable type>::]<securable>]
TO <principal>
[WITH GRANT OPTION]
[AS {<group> | <role> }]
The ON clause is omitted for database permissions and server permissions, which apply to the current database or server, respectively, as a whole. The <securable type>:: syntax is not used for permissions on database objects such as tables, views, and stored procedures. The WITH GRANT OPTION and AS {<group> | <role> } clauses are optional in all cases. The former gives the grantee the ability to grant the permission in turn to other principals, and the latter indicates the name of a database group or role that the granter belongs to and that has the authority to grant permissions on the securable.
The syntax for DENY and REVOKE follows the same basic format.
Source of Information : Apress Accelerated SQL Server 2008
|
|
0 comments
Post a Comment