Join Gerry O'Brien for an in-depth discussion in this video Permissions applied to securables, part of Securing SQL Server 2012.
As we have learned in previous lessons, SQL Server permits the assignment of permissions to securables, for the purpose of controlling the actions that users can perform against that specific securable. The permissions have very specific names, and they are applicable to very specific types of securables. In this video, we'll take a look at the different permissions that can be applied across the different securables at the server and at the database level. As an example, let's first take a look at permissions applied to securables at the server level.
Expanding the Security folder for the SQL instance and expanding the Logins folder, allows us to choose a user account for the comparison. Let's right click and choose the Properties for Tom's user account on this specific server. The Login Properties dialog box displays with the general page opened. Clicking the securables page brings up the properties for the securables that Tom is currently assigned to. Take note that the securable that is listed currently is for the SQL Server instance that we are working with.
The type of securable is the server. You'll also notice in the lower portion of the screen, we have the permissions that can be assigned to Tom for this specific server. Notice the five columns outlining the different aspects of the permissions. The left column tells us what the permission is. To its immediate right is a column called Grantor. This is where you will find out who it is that has assigned that specific permission to this user. The remaining three columns tell us whether the user has been granted the permission. Whether the user can grant that permission to other users.
Or whether they have been specifically denied. Scrolling through the list shows that, for the server securable, there are considerable amount of permissions that can be applied. Tom has currently been granted the Connect SQL permission, which means Tom can connect to this SQL Server. At the server level, if we wish to add more securables to apply permissions to, clicking the Search button brings up the Add Objects dialog. You can search for specific objects if you know the names of all of the objects in your SQL Server instance. You can specify the server itself, but we don't need to because that's already selected, or we could choose All objects of specific types.
Let's select the middle option and choose OK. The available object types at the server level are the endpoints, logins, servers, availability groups and server roles. If we wish to grant Tom some specific permissions to the Endpoints on the server, we would select the check box for Endpoints and click OK. Notice that now that we have selected those objects, the available endpoints on our SQL Server instance, are available in the securables dialog box. Selecting any one of those changes the explicit permission in the lower portion, for which permissions could be assigned to Tom.
Notice that they are is considerably less, compared to when we had the server itself. Let's cancel out of this, and go take a look at some of the securables that are available at the database level. Expanding the Master database and the Security and Users folders, allows us to get to the user for Tom in the database itself. Again, right-clicking, and choosing Properties brings up the Database User properties for Tom. The Securables page is where we have the option to choose the securables at the database level for permission assignment to Tom.
Clicking the Search button brings up the Add Objects dialog, and now we can go through the process of specifying the types of objects, or database securables, that we wish to assign permissions to. Once again, specific objects are available if you know the names of them. We can also choose all objects of specific types, or we can choose all objects that belong to a specific schema by selecting that radio button and choosing the schemas from the database. Let's go ahead and select the middle option one more time for all objects of a specific type, and click OK.
In this instance, we want to check to see what permissions we could assign to Tom on the tables in the database itself. Remember, a table is a securable at the database level. Choosing OK, the list of tables will populate in our securables portion of the dialog box. Selecting any one of these tables will allow us to specify in the lower permissions dialog box what permissions we wish Tom to have. Note that these are table based permissions. Tom could be given permissions to alter, control, delete, insert, create references, select take ownership, update and view change tracking or definition information for this table.
Again, if we go back into our Add Objects and choose a different type of object such as a Stored Procedure and choose OK, any stored procedures listed in the database, as well as the tables, because we have selected them previously, will remain listed in the securables dialog. Selecting our Stored Procedures, as you can see once again, changes the types of permissions that we could assign to Tom. Once again, if at any time, you're unsure of the permissions that you have assigned or what that specific user might be able to do.
Clicking the effective tab allows you to see the permissions that that user is currently assigned.
- Understanding security best practices
- Managing logins and users
- Understanding how SQL Server checks permissions
- Creating and assigning logins and roles
- Securing SQL on the network