Friday, March 1, 2013

Managing SQL Server Permissions (p2) - Using SSMS to Manage Permissions at the Database Level

The same type of hierarchy exists with permissions at the database level as at the server level. You can apply permissions at a high level to affect many objects of a particular type, or you can apply them on a specific object. You can also manage the permissions at the database level on a specific database user, or you can manage them on the database across many users.
To demonstrate the differences between object types available at the database level, let’s first look at managing permissions for a specific database user. As with logins, you can right-click a database user and select Properties. On the Properties window that appears, you select the Securables page, and you get a screen to assign permissions that is very similar to the login permissions screen. The difference at the database level is in the object types available for selection. Figure 6 shows the object types available when you choose the All Objects of Types choice during the addition of securables for a database user.
Figure 6. Database-level object types.

When a low-level object type such as a table or stored procedure is selected, you are able to apply explicit permissions to a specific object instance. Figure 7 Table object type is selected. shows an example of low-level securables available when the
Figure 7. Low-level database securables.

To apply permissions at a higher level in the database, you choose the object type Databases. With this securable added to the permissions grid, you can apply permissions to a group of objects by selecting a single permission. Figure 8 shows the AdventureWorks2008 database selected as the securable and the related permissions available. In this example, the login Chris has been granted INSERT, SELECT, and UPDATEpermissions to all the tables in the AdventureWorks2008 database.
Figure 8. High-level database securables.

No comments:

Post a Comment