Join Adam Wilbert for an in-depth discussion in this video Implementing server-level security in SQL Server Management Studio, part of Database Foundations: Administration.
- Creating a new user login and assigning them to a fixed server role is accomplished through the new login window. Right now, I'm logged in as assistant administrator using the Windows Authentication login that we established when installing SQL Server Express back in the first course of this series. In Management Studio, you can create a new login account by going over here into the Object Explorer and opening up the Security folder here. Next, we're going to find the folder called Logins and I'll expand that open to see all the different login accounts that are currently on this server. In order to create a new one, I'll right-click on the folder and choose New Login.
I'm going to create a new login for Maria Sontas and she's going to be established as a database creator. In order to do that, we'll come over here in the Login window here and we will establish the Login name. It's going to be "MariaSontas". (keyboard typing) And just as a reminder to myself of what we are doing here, I'm going to put a hyphen and give it the name "dbcreate" as well. So, this whole thing is going to be her login name. We can either choose to use Windows Authentication if she has a current Windows account we want to use. In this case, I'm going to use SQL Server authentication and we can type in a password here for Maria to use.
In this case, I'm going to type in the password "dbcreate", but you should go ahead and use a stronger password and something that you can remember. (keyboard typing) Below, we have some checkboxes here to enforce password policies and the password expiration. I'm going to turn this first one off so we're not dealing with any password issues with this particular user account. Now that we've established her username and password, we can assign Maria to a server role. Over on the left, we'll choose the Server Roles page where we can assign her to the dbcreator server role. This will allow her to create new databases on our server.
Let's go ahead and say OK and that will create Maria's new login account. We can see that appear over here in the Logins folder. Here it is, MariaSontas. Let's go ahead and log out of our server and pretend that we are Maria now. I'll click on this button here on the Object Explorer to disconnect from the server, and then I'll press this button over here to reconnect. This time, instead of using my Windows Authentication account, I'm going to switch this to SQL Server Authentication, and then we're going to type in the new login that we just created, MariaSontas-dbcreate. We'll give the password that we've just established and mine was "dbcreate", and go ahead and say Connect.
Now, we've logged in to the SQL Server instance as MariaSontas. You can see that up here at the very top of our Object Explorer. It gives us the name of our computer followed by the name of the instance of SQL Server we're connected to. And way over here on the right we can see that we are currently authenticated as MariaSontas. Now, let's see what Maria can do with this user account. I'm going to expand the Databases folder and we can see we have the H+Active database that we've just established. If I want to go into it though, I'll press the plus button here to expand it and you'll notice that we get this error message saying that the database H+Active is not accessible.
That's because we didn't specifically grant Maria access to this particular database. Let's go ahead and say OK to that and let's see if Maria can create a new database. I'll right-click on the Databases folder here and say New Database. I'm going to go ahead and give it the database name of just "temp" for now. Go ahead and say OK to create a new temporary database, and Maria does have access to this database; she's considered the database owner because she created it. I can go in here and I can take a look at our tables or I can right-click and choose table here if I wanted to create a new table for instance.
Let's go ahead and just close this tab. I don't need to create a new table at this point. And I'm going to come down to the Security folder that's underneath the temp database. Inside of there, I've got a Users folder and if I expand this, we'll see the different users that are currently assigned to this database. We have the database owner, we have a guest account, we have this INFORMATION_SCHEMA account, and we have the system account. Now, we're not seeing Maria's name in here because she is considered the database owner. If I come down to the other Security folder, outside of the database, which is this one out here, we'll see we have a folder for Logins and I'll expand that and we can only see Maria's login information right here.
We don't see the other accounts that we saw previously when we were logged in as a system administrator. In order to find that information about Maria's login, I can double-click on it here, and we can see the name and password that we established before. And if we click over here to the Server Roles, we can see which server role she was assigned to. If I go to User Mappings, we can see which databases she is assigned to as well. Now, I get this information message saying that one or more databases are inaccessible and they will not be displayed on the list. If I press OK, you'll notice that we are not even seeing the H+Active database here on the list because she is not assigned to that database, but she is the database owner of the temp database that we just created.
Let's go ahead and say OK to this window to dismiss it, and what Maria can also do as the database owner is delete her own database. So, let's go ahead and right-click on that temp database and choose Delete. I'm going to turn on this checkbox out here that says Close existing connections. That way, if any other users have logged in here into this temp database, it will go ahead and kick them out before we try deleting it. Now, at this point it's highly unlikely that anybody has logged in, but I'll turn this on just the same. Go ahead and say OK. And we're going to get this little error message here. Up here on the line, we have this message, which is a link.
I can go ahead and click on it to get more information about the error. And it's telling me that the execute permission was denied for Maria based off of the backup history. Now, Maria is the owner of this database because she created it, but her server role is set as dbcreator. She has the ability to create and delete databases, but she doesn't have the ability to delete the history of that database. So, let's go ahead and say OK to this error and I'll turn off this checkbox here to delete the backup and restore history information. Let's go ahead and say OK, and now the database is successfully deleted.
Let's go ahead and log off Maria. I'll click on this button here to disconnect from the server and I'll log back in. I'm going to use the Windows Authentication based off of my Windows username here to reconnect so that I gain my system administrator privileges back. So, by assigning the fixed server role of database creator to a new user, we can control the types of server activities that they can perform. In this case, we assigned Maria Sontas the role of database creator, which keeps her out of existing databases that might already be on the server, but allows her to create and manage her own databases.
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