Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Here's what the SQL Server understands about security. It knows you make a database because you want to people to use it. There are users and groups of people. There are Web applications and desktop applications that want what it is you have in your database. The SQL Server calls these principals. A principal is something that will connect to SQL Server to request a resource. They'll select from a table or update a table, or execute a stored procedure, or even create a database. And on the other end SQL Server knows about the things that exist inside of it.
The databases, the tables, the stored procedures, the functions, the schemas, the views, all these things we have worked with our considered objects in SQL Server. And yes, many objects contain other objects. Databases contain schemas. The schemas contain tables and stored procedures, and so on. But just because a principal can get into SQL Server at all doesn't mean that it should have blanket access to use and change and control to all of those objects. So in between your principals and your objects are the idea of permissions.
What can the principal do with that particular object? And we have multiple permissions that include select, insert, update, delete, the standards, but we also have execute permissions for stored procedures. We have the ability to alter or change parts of the database or control the entire database or any object in it. And these are of course different based on the person and the object. Bob might need permission to update the Sales database, but not the Customers database. Alice should be able to execute a stored procedure but not modify it.
Fred can select from a table, but not insert into it. And us, well we want to be able to log on to the SQL Server and do everything everywhere, Select, Insert, Delete, Update, make new databases, edit stored procedures, create backups, restore databases. Now if you have to define all of these different permissions and connections individually, it's going to get very tedious. But to make it easier SQL Server helps us out by defining several common roles that actually fit between your people and your permissions.
These roles are setup with a set of permissions already. You take your people or your groups and you attach them to particular roles inside SQL Server. And this makes the management process much easier. Now some roles exist for each separate database. Every time you make a database you get a new set of these roles just for that database. Those include the role called db_datareader. If you're in the datareader role for that database you can by default read from every table.
A bit better than that you have db_datawriter. You can also write to that table. You have some quite specialized roles like db_backupoperator. You might never use this but if you had someone where that was their particular job role, you could put them in this SQL Server role. And also for the database you have something called db_owner. The db_owner role gets to do everything in that database. There are a few more but this is the core of them. And because these roles exist separately for each database, you could be granted the db_owner role on one database and just be a db-datareader on another.
Now some other roles are not duplicated. There is just one role across the entire server. For example, there is a role called dbcreator. And now as you might imagine, if you're in the dbcreator role you can create db's, you can create databases. There's a securityadmin role that gets to and in fact security for this server, set up new users, set up new principals. There is a serveradmin role, and then at the top of the tree there is sysadmin. Even though there are a few others which you can explore and books online, sysadmin is godlike.
If you're a sysadmin on that server, you can do anything. It effectively makes you DB owner of every single database inside that instance. So this whole security thing breaks down into principals, roles, permissions and objects. For example, the principal Fred can be given the role db_datareader, which grants the permission to select and that's attached to the say the Orders database. Or me. Well I'll get the role of sysadmin, which gives me the permission control over everything, over the entire instance and everything inside it.
And your principals can also be Windows groups too. If you have good groups defined in your domain, you could for example give the managers group the role db_datawriter and gives them the insert, update, delete permission on, for example, the ProjectPlans database. But in the same setup they don't have permission to write to the AdventureWorks data warehouse. They just have permission to read from it. And using the built-in roles in SQL Server will make handling your permissions much easier.
Now technically speaking a role actually is a principal. But I find a bit more useful to separate them out like this. Thinking about giving that principal a particular role, which gets the permission to an object, and you still have to choose what these hookups are and you get a lot of flexibility to do that. We're going to see how to do that in SQL Server Management Studio in just a moment. Now do bear in mind, you can define your own roles. But only look into that after you've really become comfortable with the built-in ones.
Because most of the time the available ones like, db_datareader, sysadmin, datawriter, db_owner, they're going to work just fine.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64741 Viewers
80 Video lessons · 124337 Viewers
52 Video lessons · 60269 Viewers
59 Video lessons · 46100 Viewers