Quick Tutorial - Users and Permissions in SQL Server

okay I want to talk about permissions in SQL Server Commission it's a pretty complex topic and we'll be able to get into everything regarding it so I just want to go over a few things a few principle regarding permissions so basically permissions can be used to keep users from looking at certain tables or altering data in certain tables things of that nature so what I want to show you how to do is set up some users and then applying them or restrict some permissions for them so first part is creating the user I've got management studio running here to create a user it's going to be under this security area so usually when we open management studio we go into databases and start looking there but for the users we want to expand security and are actually called logins so I'm going to create a user I'm going to right click on logins and do new login there's two or three things we need to do here need to give them a name so I'm just going to do a simple name here that'll be my user more than likely we want to choose SQL Server authentication we want to click this radio button let's assign them a real simple password I'm just going to do one two three four and in the production environment we might enforce these but just for simplicity sake let's uncheck these boxes again my name this gave a password and uncheck that one more thing we need to do to create this user is give them some kind of permissions otherwise that we kind of useless to have user to do that on this dialog go to user mapping over here and then you would give them access to a database so just to do this example on the given access to a couple databases here and again we could get into some more advanced principles here but just for simplicity sake I'm just going to click the database and then give them DB own or access give them access to this database also alright so user mapping give them some access to a database then you can hit OK to create the user that's the first step now the second step is planning some specific permissions or maybe restricting some again for this or I could get in a detail on everything but I want to show you a couple examples one would be let's say for this user we only want that person to have access to certain tables not all the tables in those databases in the second example I'll get to will be maybe even restricting access on a specific field or column so let's let's restrict the users access to a particular table so we've created a user and I've given them access to this database down here but let's look in that database I've got a couple tables in this database so let's say for the sake argument we wanted to see this table but not that table the way to do that right now they can see both because I gave them access to this database so not to be able to view this table I'm going to drill down into the database find that table and right click on it and go to properties so this is still assigning permissions we create a user we give them some permissions but this is a finding more specific commission so i right-clicked here's the copy page for that table and i'm going to click this permissions tab over here on the properties and then ignore this year's or we're going to do a different user and do some stuff with that so in your when you do this you may not have any users here that's fine so I click Commission's and what you want to do is for the users do this search and find that user and sort of add them and then you can find permissions so I'm going to click this search button here you know easiest thing to do from this dialog is to hit browse your users should show up if you have access to this database so let's select Bobby hit OK and hit OK on this what I want to do again for this table I don't want this person to have permission so what we're actually doing here is sort of withdrawing the permissions to this particular table so I'm going to click on Bobby and I'm going to check deny in all these boxes and then hit okay so we'll test this in a minute but now if we were to log in as Bobby you should still go to see this table but you shouldn't be able to see this one or view the data in it based on those permissions we just assign in sort of withdrew second example I want to show you which is could be pretty useful is restricting a user or a group of users permission to a particular field they don't want to see a table but we don't want to allow them to see a specific field in that table just all the other fields will show you how to do that once again I'll be done similar to what we just did so I go into this database and find the table and for the sake argument I'm going to say I want the user to be able to see a couple of fields in this table but not a specific one like a social security number so once again right-click on the table go to properties pretty much the same thing we just did but there'll be one important difference to look like permissions search for Bobby hit browse here check that box hit OK so this time around what I'm going to do is on the sign a select permission which basically means Bobby can select or view the data in the table so the way the way to do this click on select down here and when we do this this button becomes usable so notice it might be grayed out but if I click select here now we're going to actually assign some pretty specific permission so I'm going to click this column Commission's and I want to say grant on these two fields but not on this one so I'm going to say deny on this one all right so I checked grant for the field I want Bobby to be able to see review and deny all the ones that I don't want them so they could be to a three field each depending on what table and fields you have it okay on that and that's it just hit OK on this as well so that's it really what I've just shown you is creating a user finding some permissions to the user and then restricting some permissions for particular tables or for particular fields or columns in a table just so you see how this works if we look back to that let's just see how this works we just show you what happens if we log in in body it disconnect here and I'm going to log back in as Bobby just we can test this out see if his permissions got taken away or if he can see certain things and not other things so let's test that first one first let's go in and see if he can look at one of those tables but not the other so he clicks on this table and try to look at it this gives a permission denied here so basically they keep that person that particular user can't do that table but notice he should be able to still see this one right so we've assigned some Commission's to restrict certain stuff in our database let's try to do another one ounce as you can see how it works we go into this keep on database I'm going to quick query this for the sake of speed I'm just going to let this be then create a query for me and then I'll modify a little bit so right now is trying to look in that table at all three fields and it's getting an access denied so what I'm going to do is modify this to say okay this is Bobby maybe he is just going to go see these two fields but not that third one so let's try this and notice now for this particular user they can see certain fields but not others based on the ratio of permissions that we applied you

Channel: Keil Jones

Views: 91,248

Rating: 4.7601714 out of 5

Keywords: how, add, how to, 2016, assign, table, database, management studio, gui, permission, select, create, column, field

Id: Mkw7tRfh2_8

Length: 10min 54sec (654 seconds)

Published: Sat Feb 25 2017