Join Adam Wilbert for an in-depth discussion in this video Granting access to a database, part of Database Foundations: Administration.
- Adding permissions to users follows a hierarchy that controls where they can go and what they can do in the database. After determining what, if any, permissions the user requires at the server level, the next step is to determine their needs when it comes to accessing specific databases. Let's continue working with Maria's user account, and we're going to give her some permissions to access the H+ Active database. Here I've logged out of my SQL Server instance, and I'm just going to log in as Maria real quick here. I'll change the authentication to SQL Server authentication. We'll type in Maria's username, and her password is 123 right now.
Go ahead and press connect, and that will connect to the server as Maria. Now let's take a look at our databases. Let's expand the databases folder, and I can see the H+ Active database here. Let's try and expand that, but we get this error message saying that the database is not accessible. So, let's log back into our system administrator account, and give Maria some increased permissions. I'll log out of the server, and the press the login button to get back to here. Then I'll change my authentication back to Windows authentication to use my Windows account, which is the system administrator. I'll press connect here.
Then I'm going to come over to the security folder here. I'll expand the logins folder, and I'll find Maria's account here. I'll double-click on it to open up the properties for Maria, and then we're going to come over here to the user mapping section here. In this section, we're able to choose which databases that Maria will have access to. I'll place a check mark here next to the H+ Active database, and you'll notice that when you do, all of the database roles show up down here at the very bottom. Right now Maria is currently assigned to the public role in the H+ Active database, and we'll look at the what the other ones here mean, in just a few moments.
Let's go ahead and press the OK button, and that will update Maria's permissions. Once again, let's log out and back in as Maria. I'll change my authentication again to SQL Server authentication. The login name is Maria and the password is 123. Now, let's go back into the databases folder. I'll expand the H+ Active database, and we can get into the database now at this point. Let's expand the tables folder, and you'll see that we don't actually see any of the data tables within the database. If we right-click on the tables folder and try and create a new table, you'll notice that we also can't do that.
That it says certain edits require CREATE TABLE permissions. Let's go ahead and press OK to dismiss this dialogue, and then it does open up a new create table tab here, but if we were to make any changes it wouldn't let us save it anyway, so let's just go ahead and close it out. So just granting access to a database, doesn't actually give you permission to do anything there automatically. There's another level of security involved that we'll need to enable. When we granted Maria access to the H+ Active database, we saw that she took on the permissions assigned to the public role, but that there were other role options available as well.
These are called fixed database roles, and we'll look at those next.
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