Join Adam Wilbert for an in-depth discussion in this video Solution: Assigning object permissions, part of Database Foundations: Administration.
- I hope you were able to give Angela the Permissions that she needs in order to get her job done. Here's how I'd go about modifying her login account. First, I need to make sure that I am logged in as the system administrator, which I am. Then we can go into the database and find her user account. I'll double click on it and then switch over to the Securables page. I'll press the Search button to add in the different objects to my list here, so I'll choose All objects of the type and choose Tables and Views. That will populate this list over here. So the first thing that Angela needs is the ability to read data from the Customer Invoices and Line Item tables as well as the Line Item Details view, and because she needs to read data, that is a Select Permission, so we'll go and choose the Customers table and Grant the Select Permission here, the Invoices table, and we'll Grant that Select Permission, the Items table, the Line Items table here, we'll Grant that Permission, and the Line Item Details view, we will Grant the Select Permission so she can read that information as well.
Next, she needs the ability to modify the quantity of a product that was ordered. We can find that in the Line Items table is where the Quantity field lives. Now we want to apply an Update property to that, but I don't want to update all the different fields. I just want to update the Quantity field, so instead of Granting Permission to the Update Permission, I'll select this Update line and then choose Column Permissions. In this case, I just want to Grant Update Permissions to the Quantity field, and we'll Deny Update Permissions to all of the other fields in the Line Item table.
Let's go ahead and say OK to that, and then we'll Grant her those Update Permissions that she needs. Finally, she needs the ability to add in new products and make modifications to the design of the two product-related tables, and to me, that's telling me that I need to give her Alter and Insert Permissions on these two tables here. So the Product Listing, we're going to give her some Alter Permissions. I'll scroll up to the top. I'll Grant that Permission there and also the Insert Permission so she can insert new records into that table, and we'll do the same thing for Product Options, so we'll Grant her Alter Permission so she can change the design of the table and Insert Permission so she can add in new records into that table.
So that's how I would modify Angela's account permissions. We'll go ahead and say OK to save all of those changes, and I hope you came up with a similar solution.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database