Join Adam Wilbert for an in-depth discussion in this video Granting object-level permissions in SSMS, part of Database Foundations: Administration.
So far, we've granted Maria access to the H+ active database and we've given her read permissions on all of the tables and views through the db data reader fixed database role. I've just found out that Maria also needs the capability to edit records in the employees table but should not be permitted to edit any customer or product information. We can do this by assigning some more specific permissions. In order to do that, I first need to log into the server based off of my sysadmin account. So go ahead and connect using that. Then, we'll come into the databases, we'll find the H+ Active database, and I'll expand the Tables folder.
The table that we want to allow Maria to edit is the employees table. So I'll right-click on it and choose Properties at the very bottom. Next, we'll go up to the Permissions page here on the left and then over here in the Users or roles section, I'll press the Search button to add Maria to this list. I can come over here and press the Browse button and then we can place a check mark here next to Maria to add her to the list. Press OK to that, and press OK once again. Now Maria's user role is listed right here and we can come down here and assign specific permissions for Maria. The permissions that we want to grant are the Insert permissions.
So I'll come over here and place a check mark here in the Grant column next to Insert. And we also want to her to be able to update our employees so I will scroll down and place a check mark here next to the Update row. Now what if we wanted to have even finer control on what Maria can modify in our employees table? Let's say that all she really needs to do is maintain the benefit and position information on the employees but shouldn't be able to change their name or email address in the database. We can do that too by selecting the Update permission here and then coming up and pressing this Column Permissions button at the top.
That will allow us to grant specific permissions on just the columns that she needs access to. In this case, I'm going to grant permissions on the Benefits column and the Position column and I'm going to deny the permissions on the other columns in that data table. Let's go ahead and press OK to make that change. And notice if I come back up here to the Insert row, you'll notice that I don't have access to that same Column Permissions button. And that's because inserting and also deleting records automatically applies to all of the columns across the whole table. If we want, we can switch over to this Effective tab and we'll get an overview of all of the different permissions that Maria currently has.
Now, we can see that she has select permissions on all of these different columns in the different tables here. We're not seeing the changes that we just made though. So let's go ahead and press OK and then we'll come back into the employees table. I'll right-click and choose Properties, once again. We'll go back to Permisisons, and choose Maria, and then choose that Effective tab again. And now we can see the changes that we had just made. So she has insert permission here, we have select permissions on all these different fields here, and we have update permissions on the benefits and position fields. Let's go ahead and say OK to that and we'll log out and see how Maria can make use of these changes.
I'll change my Authentication mode back to SQL Server Authentication. I'll change the Login to Maria. Her password is 123, and I'll press the Connect button. Now we'll go into the Databases, we'll expand the H+ Active database, and then the Tables section, then I will right-click the employees and choose Edit Top 200 Rows. First, let's try and change Brian's name here to Thomas. When I press Enter, you notice we get this message here saying that the row was not updated because we don't have the update permission on this particular column.
Let's go ahead and press OK to that, and I'll press Escape to return the name back to Brian. So Maria cannot edit the FirstName column but we can edit the position so I'll come over here to where it says NULL under Position for Brian Finley and we'll assign him the position of Executive Assistant. And notice that when I press Enter that time I don't get any warning messages and the database accepts the change of Brian Finley's position to Executive Assistant without any complaints. So not Maria's role within the database is getting further defined. She now has read only access to all of the tables, and with the help of some specific object level permissions, the ability to maintain our employee position and benefits information as well.
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