SQL Server - Setting up User Security

uh next I want to show you how to set up some logins here in the server uh so for different users and give them different privileges so at this point we're going to expand our server we're going to come down to uh security and expand that then go into our logins we're going to right click on logins and we're going to pick on um a new login all right so you're going to give this a name was called um class one and uh here you can get assign them a password and uh what would be really important is to give them the default database so uh let's cck on the class one manual database now uh another thing that's going to be important is to rot the server roles so we have a couple pages in the book about the ser roles you can you can describe those most people are just going to be public users but you you would give them additional permissions over here as well uh everybody is a public user as a default uh now what's also going to be important is the user mapping you want to give them uh access to their database we pick on class one manual and then you don't want to give them access to the other databases of course that they they wouldn't um that wouldn't apply to them and then each database you can see they can have the database roles there's the server roles and then there's the database roles all right and um uh you can see those so the server roles is for the entire server whereas the database roles down here are for the individual database right you can get people permissions in that way as well but it's okay to get just make somebody a public user and uh at this point we're going to click on okay and now that that username has been created as you can see uh let me now show you how to do uh table level security using that username so we're to expand the class one manual database and let's say we want them to have access to the employees table and you're going to right click on the table and then pick on properties and then you get this kind of screen you can also assign the the security at the server level at the database level at the table level for views all you'll do is you right click on your op object and you pick on permissions like we're talking about here or you pick on properties in this case I'm going to pick on permissions I'm going to pick on uh [Music] search and then we pick on browse and this should show us our different names here here's the one that we' just added in there it's called class one and you can do more than one if you wanted to so I'll click on okay and we'll click on okay from this screen now watch what you're going to do you're going to give them specific permission so let's say for class one uh I want I don't want them to delete so I'm going to deny the delete I'm going to grant them the insert I'm going to grant them the select and I'm going to grant them the update permissions okay but you you deny specifically what you don't want them to have and then you grant specifically what you do want them to have everything is kind of on deny mode uh inherently but you know you want to Grant what they're supposed to have and then uh deny what they're not supposed to have Okay so let's see this means they should be able to insert records select records and update but not delete so let's click on okay and now to really test this I'm going to close out of the SQL server and we're going to go back into the SQL Server except this time we're going to log on as the different user this is really important that to demonstrate this so for the log on I'm going to type in the one that we just made and we'll type in the [Music] password I'm going to remember the password here so I'll click on connect and now uh there's a feature which I'll have to show you where it's asking for a new password automatically so type a new password and verify that okay so now let me expand my datab basis uh let's go into class one manual let's expand the tables and notice how that person only has access to that one table which is exactly what we want to see uh we're going to go ahead and and try to edit that table and you're going to see that it does let us edit that okay now I should be able to change a person's name I'm going to type in my name here and you see it did let us edit that record I should be able to add a new record [Music] and it lets us add a new record uh so that's fine now it shouldn't let us delete a record so I'm going to right click on a record and try to delete says you're about to delete one row click on yes and this is where it does not let us have deleted because of the permissions uh that we apply to that cable all right the the permission was denied on the object and so that was because of the user level security uh we're going to close this window there now uh actually we're logged on as um the user so now we want to log on as sa again so I'm going to close that of SQL Server we're going to go back in as sa and now I'm make sure you pick on the log on that would be essay and then we're going to uh log on [Music] again and then if you want to remember the password can do that pick them Connect Now allow them as sa uh now only the SA or people who are set up as administrators can actually apply the security let me show you how to do field level security so I'm going to go ahead and uh expand the databases we're going to expand the manual uh database we're expand the tables let's go back into the employees table again I'm going to right click on employees and pick on properties we right back to the screen again we going to pick on [Music] permissions all right let me show you how you can go down to the field level I'm going to pick on the class one I'm user that we just did and we're going to pick on update now notice what it says over here it says column permissions so that means we can let them update some columns and not others uh so let's pick on that so so I want them to update uh I want them to update the address the city the country maybe the title but I don't want them to do anything else so I don't want them to update the birth dat I don't want them to update the employee ID or any of these other fields okay so uh our test would be if we can update the address that's fine but I don't want them to update these other these other fields uh City and country is fine address uh we'll give them the region the region is actually um the state here and the postal fod uh we'll give them as well that's all I want this person to be able to update so we're going to click on okay so watch what we did we C on update and then the column Comm Merion right there let's see how this is going to work we're going to click on okay again I'm going to close out of this now you could be connected at the same time we can certainly connect uh to the database engine at the same time but uh just to keep it clean I'm going to close that um SQL Server let's go back into the SQL Server this time I want to log back on as class one all right we're going to log on this class one click on connect now we're back in again so now we'll expand our security I mean we'll expand the databases we'll expand class one manual expand the tables we're going to right click on the table and then do a SE [Music] edit all right I'm want to try to change this person's last name and when I try that doesn't let me do that you see this the permissions uh kicks in all right so I'm going to hit the Escape key to go back to its original value however if I go over to the [Music] address and I try to change that that is how it does let me update that uh let's try to change um the home phone number I don't think we we we got that one so I'm going to type in 2155551212 and I try to uh update that notice how it doesn't let me because I I I took that permission away from the user so that literally I can only change uh like the address the ZIP code oh the uh the phone number is still like that so let me hit Escape here uh and then we'll go down and we'll going to go change the country so let us do that so this is how we can do field level security and uh so we set up logins I shows you how to go down to the table level and also down to the field level