Once you have logins and users created, you control what they do through permissions. In this lesson you'll learn how to apply and evaluate user permissions
- [Instructor] In thinking about SQL server security and the multiple tiers that are available, we know that we have security at the server level through logins and server roles and we also have security at the database level through users and roles. Once we've created the logins at the server level and created users at the database level, we can now start to consider applying permissions that effectively control what these users can do and can not do on our system.
Let's focus on the permissions for logins at the server level. Now before we step in to applying all of our permissions that we want to for our test user, it's important to understand that the complexity of dealing with permissions on SQL server are quite large and to give you an idea, if we step out the msdn page for the permissions for the database engine, you can see this wonderful chart that is known as the SQL server database engine permissions chart.
It's very complex. It's goes in a bunch of different directions and it shows you just how complex permissions are on SQL server. However we can make the management of them complex or simple. Scrolling a little further down, you'll see that there are specific naming conventions used such as control or alter or alter any. Understand these. So come to this page, read through these and understand that when you apply one of these permissions, what is specifically means. Also, if you come down a little further, we can see that there are permissions that are applicable to specific securables.
Now securables in the database could be any database object. Again the type of permission and what it applies to is listed in this table and finally if we scroll down a little further, we can actually see another table that lists all 230 SQL server permissions. So there are a lot of permissions to consider and to go through. Again, use this as a resource and a reference to understand them. But let's go ahead and see how we can apply some of these permissions at the server level.
So here's a server level login called test user one. Double-clicking and opening the properties page for this user allows us to come in and work with the permissions and typically we do so through the securables option and if we look at the securables, these are really applied at the server level. So every permission that you look at here will be a server level permission. If we choose to allow this user to alter any connection, we can grant that permission and the user is now given that permission so they can alter any connection to this database.
If we provide the with grant permission, then that means that this user not only can alter any connection but they can apply this same permission to other users. Now we also have this thing called an effective tab here. If I select the effective tab, we can see that the current permissions are alter any database, connect SQL, create any database and view any database because we've applied certain specific permissions. Now sometimes you'll have to click the OK button to make the application of those permissions and then come back in and look at those securables again.
So if we look at the effective permissions again, you'll notice that we now have alter any connection. So that was the one that was added by us selecting the grant permission here. Here's one key that's very important to note when it comes to permissions on the server and at the database level. A specific denial will override any other granted permission so if we specifically deny alter any connection, you'll notice that SQL automatically clears the grant and the with grant and if we choose OK to make that permission applicable, reopen the test user, go to securables and effective permissions.
The alter any connection is no longer available. So this is just an example of applying a permission to a user account at the server level so we've modified the login properties for a user by working in the securables tab in granting or denying permissions.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options