From the course: Microsoft SQL Server 2016: Installation and Administration

Manage roles - SQL Server Tutorial

From the course: Microsoft SQL Server 2016: Installation and Administration

Start my 1-month free trial

Manage roles

“

- [Instructor] Securing your database and your database server are important considerations for any installation of SQL server. In this video, we'll focus on server log-ins and roles. And it's important to understand how these log-ins and roles at the server level affect the security in your SQL server installation. So to start with, we'll expand our security folder. And you'll notice that we have various folders underneath, such as log-ins and server roles and credentials, et cetera. If we want to take a look at the server roles, we can expand this folder and we'll see the list of server roles that are available on this SQL server instance. Now, we'll take a look at some of these as we demo here in a moment. But if you want to get a better idea of how these roles impact the server and the permissions that they will assign to users that become a part of these roles, then we should take a look at the server level roles page on the MSDN website. When we get to this page, scrolling down displays the table for the fixed server level roles. Here you will find each of the roles listed in the left column and the description of what permissions this role will give users who are assigned to that server role. Let's step back into the management studio SQL server again. And use one that is most common, such as public. Users that are not assigned to any other database server role, are added to public by default. In here, we can see that we have specific securables that are assigned to public. And in this case, we can see servers and this is the install that we have. And we can see any specific endpoints. In this case, for transact SQL statements such as default TCP, default via, local machine and named pipes. Down on the lower section of this dialog box is where we see the permissions that are available. Or should we call them explicit permissions that are available for this particular SQL install. And what the different columns mean, the permission talks about your permission type. So administer bulk operations, alter any credential, et cetera. Those can be set up to have a grant permission, a with grant permission or a deny permission. A grant permission means that we have assigned that permission to this role. Which means it can perform that action. A with grant means that not only can you perform that role but you can also grant that permission to other members of the role. And then deny means that that specific permission is denied. Let's expand this a little bit more and take a look at some of the permissions that we could assign to the public role. If we said "alter any database" and we provided the grant permission and choose okay, any members of that public role now will have that permission. So we can see that we have specific permissions for this server, alter any database, has been granted. And you'll notice that the change now shows the SA, or the system administrator, was the grantor of that permission. Let's clear that check box one more time because the public role is one again that we want to be careful about because anybody who is not assigned to one of these other specific server roles falls into the public role. And it's the one that you should have the least amount of privileges assigned to. That's a look at the server roles on our SQL server security considerations. However, the roles themselves are only half of the story. We still need to focus on the log-ins and how we apply the log-ins or assign the log-ins to the server roles. And we'll cover that in the video on log-ins.

Contents