Join Adam Wilbert for an in-depth discussion in this video Granting user permissions to objects, part of Database Fundamentals: Administration.
- We just saw how we can assign specific object level permissions by going through the individual database objects and connecting them with the user logins. We can do the same thing by starting with the user and assigning database object permissions to them instead. Let's go find Maria's login and see how this works. I have logged back into my instance of SQL server using my system administrator account, and then I'm going to come into the database folder, find the H+ Active database, and come down to the security folder. Then I'll expand the Users folder, and find Maria's login here. Let's go ahead and right click on her and choose properties.
Then, we'll switch over to the Securables page here on the left. Here, we can see which tables she's currently assigned to. We can see that she has some permissions based off of the employee's table here, we can see what those permissions are down at the bottom. Let's come up and press the search button here to add additional tables into her Securables section. I'll choose the second option, to add all objects of the types, and then press ok to choose that I want to see all of the different tables from the database, and press OK. You'll see all of the different tables get added over here under the securables section.
Now, we can click through each of these different tables in the database to see what permissions Maria has on these different objects. If we flip over to the Effective tab here, we can verify which permissions she currently has on these different objects. As you can see, she has select access on all of the different tables due to her fixed database role as a DB data reader. If we come back up here to the employees table, you'll see that she also has insert and update permissions on a couple of these fields. Let's go ahead and press cancel without making any changes to Maria's permission settings. That will return us back here to the object explorer.
Rather than going through each database object to see who's been assigned permissions, we can investigate individual user accounts in one convenient location. From the securable page of the user logins property window, you can see all of the database objects that they've been granted permissions to, and even add additional object permissions, or modify their existing permissions.
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