SQL Server 2016 Part 4 - Databases, Logins, Users, Roles and Schemas

welcome to part 4 this tutorial series on sequel Server 2016 this part of the series is going to explain a bit about sequel server security so we're going to explain about databases logins users roles and schemas so the video is primarily about security and how you connect to databases but we're going to get started with a quick look at how you create new databases and a few the basic properties you can modify when you do that so we'll explain things about the database owner and a few of its other properties and then we'll have a quick overview of the main database objects the second half of the video is going to focus on the security aspect so I'll start by talking about server logins and connections then we'll talk about users and databases then we'll look at the various server and database roles that you can assign to users and logins and finally we'll finish off with a quick look at what schemas are and how you can use them to control permissions so quite a lot to do here it's a fairly complex media fairly boring video to be fair but it's nice background information just before we get started finally creating databases so let's get started in the previous video in this series we looked at some of the system databases that get created automatically whenever you install sequel server we're going to start this video by showing you how you can start creating your own databases and the begin with I've already opened up the sequel server management studio and I'm just about to connect the sequel 2016 training instance that we created in the first video in this series if I click the connect button that'll give me access to the databases folder now one single instance of sequel server can hold up to thirty two thousand seven hundred and sixty seven different databases obviously not going to create anywhere near that many for the purposes of this tutorial we just want to create one to start with and there are a couple of different ways you can create databases but the simplest most convenient way to show you to avoid having to learn how to write much complicated code is to simply right-click on the databases folder choose new database to launch the new database dialog box now technically all we need to do here is enter a sensible database name and then click the ok button there are several other options available on the dialog box there are a couple of different pages to go to and lots of properties to browse through but all the default options will be absolutely fine for creating a simple database so what I'm going to do is click into the database name box and give my database a sensible name now there are a few simple rules to follow when you name databases but you won't go far wrong if you just stick to a combination of letters and numbers try not to use punctuation characters although some are allowed that include spaces actually you're allowed to use spaces in the names of your databases you will have a slightly easier time of things later on if you don't use spaces in database names saves you a little tiny bit of typing later when you're referencing your databases so I'm going to think of a really inventive sensible mode my database I'm going to call my test database and then having done that all I need to do is click OK down at the bottom of the dialog box and that will create my database for me we should say the test database appears in the databases folder now it's all a bit so easy really isn't it then what you do is complain about now because we're logged in as a system administrator for this instance of sequel server it's just as simple for us to delete the database as it was to create it in the first place a process was caution in the real world but just for demonstration purposes we haven't added any objects to the database yet we can happily right-click on it and choose delete that will display a dialog box and all we need to do at that point is click the ok button and that will delete the database and of course any objects contained inside it including all the data so once I click OK the database is now gone now we saw that the work quite a few other options available when you create a new database and although you won't need to change many if any of them devolve this tutorial series it is nice to know at least a little bit about some of the basic options so let's write the date Bates follower again and choose new database and we'll get the new database dialog box reappearing I'm going to create a new database this time I'm just going to give it a slightly shorter name I'm going to click test DB again without any punctuation characters or spaces then let's have a quick look at some of the other options available so the first option we've got below database name is called owner now ownership is a concept in the sequel server whereby every object must be owned by something and the database owner in this case will have unrestricted permissions to do absolutely everything with the database the the only set to the default at this point which if you followed the install process that we showed you in the first video of the series will currently be you but your Windows user account just to show you what other options you can choose here I'm actually gonna have to close down this dialog box for a moment I'm just going to cancel the dialog box and open up the security folder which is the second node down from the databases folder in the in the server list so I go to the security folder and then just expand the logins folder I'm going to talk a little bit more about login shortly but if I then go back to the databases folder and choose new database and I'll put in a database name again choose test DB then if I look at the owner and look at the little ellipsis button on the right hand side I can click on this to see a list of other types of things I can select from so you'll see that the the object types I'm allowed to use for the database owner is one of the logins from this logins security folder if I click the Browse button here will show me the same list that I'm just looking at in the logins folder here so the default is the system administrator account which as I say should be your Windows user account if you are following the same process as we went through in the first video of the series I'm not going to bother changing that there's no real reason to do so in this case I'm just going to cancel out that dialog box and leave the owner as the default the next bit of information the dialog box shows you the names of any files that will be created when you create the database so two files get created by default when you create a new sequel server database the first one here is called test DB and that's the main data file for the databases is type there says rows data then there's also a log file that gets created which stores a transaction logs which keeps track of any changes made to the data when you insert records or delete them or modify them just to show you where those files live you shouldn't really need to see this or manipulate these in any way but if I open a Windows Explorer window and then browse the route to my C Drive and then into Program Files and then I'm looking for the folder in which I installed Microsoft sequel server which conveniently enough is called Microsoft sequel server and then in there I've got a whole bunch of different folders corresponding to different versions and instances of sequel so the one I'm looking for here is called OS SQL 13 sequel 2016 training so obviously this is related to the instance of sequel that we installed in video 1 and mssql is the database engine component as opposed to MSRs which is the reporting services and a S which is analysis services so if I go into the ms SQL folder and then the ms SQL folder in there and then finally into the data folder in there I'll see I've got a great big long list of different files corresponding to in this case all the system databases now you remember these hopefully from the previous video we have the master database model ms DB and temp DB and each one of these has got at least one single primary data file and one single transaction log file the temp DB database has actually got multiple different data files we've got a bunch of secondary data files as well so for large databases you'll you'll often find and you can do this yourself as a as an administrator you can create multiple different secondary data files spread them across multiple locations we're not going to do that in our case of course we're just going to show you what happens when I click the Add button to create this database I'll find that I get these two new files created and living in that folder they'll get deleted or as well if I go back to the server list and I find my testdb and right-click and delete it just as I did before when I click OK and then again browse back to my data folder I'll find those two files have now been deleted as well just a few other options worthwhile mentioning if I close down this Windows Explorer window and then go back to create a new database yet again I can right click and choose new database and I'll call it test DB one more time and then let's have a look on the options page of the dialog box the first option here is called collation and this is to do with how text is treated when you do simple operations like sorting you may remember one first installed sequel server in the first video in the series that we set the default collation to latin-1 and then we chose that it was case insensitive and accent sensitive so I've scrolled down far enough I'll find eventually that same setting latin-1 general case insensitive CI and accent sensitive should you decide that for this particular database you want any other type and we'll talk about the implications of some of these things when we are when we are writing queries later on in the series then you can feel free to choose a different option for now I'm going to stick with the default just to quickly mention what the other main settings here are for recovery model affects how the backup and restore feature works so we're using a full recovery model for this database meaning we'll get the maximum amount of information about transactions that occur in the database compatibility level effects the latest version of sequel server supported by this database so ours is set to sequel Server 2016 which is fine were in sequel server 2016 for this series then we've also got containment type contained databases are databases where essentially as much information as possible is stored in a single entity making it more easy to port to different servers so for instance user information and login information is stored in a single database rather than spread across the system databases and the actual database itself I'll talk a little bit more about contained databases when we talk about users and logins a bit later on in this video so for now which you can leave the containment type sets and non now obviously there are lots more properties available down at the bottom of this dialog box and I'm certainly not intending to go through every single last one of these properties that I make from an even more boring video than the one you're watching so what I will say is that if you want to find out any information about what these properties mean there's a dedicated Help button at the top of this little viola box if you could be helped button that will launch you into a web browser and take you off to the Microsoft developer Network site and then you'll get a page dedicated to this page of the dialog box and each one of those individual properties is explained in small amount of detail but with further links to give you more information by how they work in a huge amount of detail so I'll leave that up to you I'm not going to change any more settings I'm just going to close down that web browser and then all I'm going to do heading back to the general page making sure on creating testdb with a default owner click OK to create our basic test database that we use for the next part of the video now we mentioned in the previous video in the series that any new database you create is based on the models template database and that's one of the system databases if I expand the system databases folder the model database is sitting in there so whenever you create your own new database any objects that belong to the model database are copied into the new database so let's have a quick look inside our test DB database to see what's being created for us now there are lots of different types of database object that we're going to be covering in detail both in this video and later parts of the series but just to give you a quick flavor for what you should expect to see in the various different folders here tables of course are the most important parts I think of the entire database tables contain the raw data of your database all the rows and columns and individual bits of data that make up your database you won't find much in the tables folder at this point apart from a few empty system generated table so that system tables file tables and external tables unless of course there were some tables inserted into the model database when your database finally does have some tables in it database diagrams are a great way to create a visual representation of how those tables are related so in a later video in the series we'll talk about creating relational databases and joining primary keys to foreign keys and other exciting sounding techniques like that and will use database diagrams to show a nice visual representation of how those things are related a view is often described as a virtual table so again when you do actually have some real tables in your database you can use a view to pull together information from multiple different tables and present it together in a single continuous well view the name is fairly self-explanatory will create views again a bit later on in this series but for now you'll be you may be interested to know that there's quite a lot of system views already in your database if you expand the views folder then expand the system views folder there's lots and lots of information already stored in here now these take information from some of the system tables are part of the the sequel server instance that you've installed so again we'll talk about these in a bit more detail later on for now I'm just going to collapse the system views and then collapse the views folder as well we're not going to talk about external resources or synonyms much if at all in this series so that leads us next on to the wonderfully named programmability folder now we actually already have a bunch of videos from a previous series which describes how to create many of the object types stored in here so you got things like stored procedures which are essentially programs that you can write that are stored in your database and can be executed to perform various tasks we've also got functions of various different types like table valued and scalar valued they can return tables of data or they can return values if you call them in your queries as I say we've already got videos that describe how to create many of these things so we'll probably create some slightly more updated videos just as part of this series which go over this topic again but for now if you're interested we've got an entirely different series all about sequel server programming so let's leave that folder alone for now for now now I'm not going to cover the service broker or the storage folder as part of this series so that leaves us just with the security folder right down in the bottom of the list if I expand that you'll see there's a variety of items in here and these are all used to control who's allowed access to and what specific things they can do to various objects in the database so it's quite an important folder and as we're logged in as a system administrator we've got access to manipulate many of these these things there are three main items here there's users roles and schemas and for the second half of this video I want to explain quickly how all these things fit together and what basic things you can do to manipulate these as a system administrator okay so let's get some of the basic ideas out of the way first if you want to be able to connect to a sequel server you must have a valid login stored in logins folder of the security folder which is stored at the server level so I just collapse the databases folder for the time being just so we can focus more clearly on the security folder we've got this logins folder list out all the valid logins currently added to the server if you remember when we installed sequel server in the first video of this series we assigned ourself as the default system administrator so you should see an account related to your Windows user account here the other items that you'll see in this folder will be related to various other users and groups and computers and also default service accounts for the various aspects of sequel server that have been installed you might also be able to make out the SA account which is the default system administrator account for servers running in sequel server authentication mode we'll talk a bit more about authentication modes in just a moment now each login on the server must also be associated with a server role and the server role determines what this login will be allowed to do on the server so if I expand this server roles folder you'll get a basic idea about what each role is for based on simply their names but if you want more detailed and a quick Google for sequel server server roles we'll give you much more detailed information about each of these sysadmin is a fairly simple one to explain no system administrator this is the role that we're assigned to by default if you installed sequel server the way that we did in the first video in the series if you did want to modify the server roles for any login or you wanted to add a new role then what you can use right click on our login and choose properties and then on the dialog box that appears you can choose the server roles page and see which roles that login is associated with so of course as we said where is so assigned to the sysadmin server role and we also assign to the public role now although I can remove myself in the sysadmin role I don't actually want to do that I can't I find that I can't remove myself in the public server role so any new login that you creates is assigned to the public server role by default I'm going to click cancel I don't wanna make any changes to my own server roles at this point and what we'll do next is will create a new login and assign a server role to that new login just as for creating databases so there are a couple of ways you can create new logins but the most convenient way for this video is to show you how to use the dialog box to do it so just like for a database if you right click on the logins folder you can choose to create a new login and when you do that you'll get a new login dialog box the first thing we need to do is specify a login name and the exact way you do that depends on which authentication mode you're using if you remember the first video in this series we configured our sepal server installation to use windows authentication mode that's the more that's recommended by Microsoft as is more robust and secure if you've got Windows authentication mode set then essentially what you need to do here is enter the name of any user or group or indeed use the search button here to launch a dialog box it helps you select users or groups I'm not going to do that in this example because it's going to mean logging in and logging out to different user accounts in this machine to demonstrate what I want to demonstrate so just temporarily what we're going to do is generate a sequel server authenticated login so I'm going to switch to sequel server authentication and then I'm going to create a brand new login name and you can make this up another exciting name I'm going to call it test login and then we'll need to enter a password for the login as well so this won't take any information from our Windows credentials of course the user will need to enter a password when they choose to connect to the server so make your password nice and robust and and a strong password using all the standard techniques you'll have heard about the creating strong passwords I'm going to create one that's reasonably easy to remember and I'll need to enter that twice of course so let's just do that quickly there are a few other options associated with the password so you got this option here to enforce a password policy which if that box is checked it simply ensures that you've got complex passwords involving a combination of numbers and letters and doesn't include the username in the password and so on you can also make the password expire and ensure that the user must change their password the next time they log in I'm just going to uncheck the enforce password policy box just for the purposes of this demonstration now I want to keep things quick and simple just to demonstrate the things I need to demonstrate here down at the bottom of the dialog box you can see that you can change the default database that's opened when the login connects to the server and also the default language that they use there's no point in really here changing these from their default I'm going to leave those as they are what I'm not going to do is head briefly to the server roles page just to show is that again we're assigned to the public server role and I can't uncheck that and I could assign this user or this login I should say to any other server role as well but this example I want to keep this purse this particular login in just the public server role so that point I'm going to click OK and I'll find that my new test login has appeared in the logins folder now one small problem with the login that I've just created is that it's not using Windows authentication is using sequel server authentication and as you remember from the first video we installed our server in Windows authentication mode so if I try to create a new connection to this same database engine and I'm going to change this so it's using sequel server authentication rather than windows authentication and I'm going to change the login so it's test login I'm going to put in my test password as well so when I do that and I try to click connect you'll tell me that I've failed to login for this test login doesn't give me much more information than that other than the error number which is worthwhile if you're trying to Google for help use the error numbers that are created for you here as well but in this case are know exactly what the error is is because this server is currently configured in windows authentication mode and not sequel server authentication mode so I'm going to click OK at this point and cancel that connect to server dialog box to allow this login to be authenticated I've got to change the authentication mode of the server so I can do that as a system administrator by right-clicking the server node at the top of the list choosing properties and then from the dialog box that appears hadn't gone to the security page and changing to sequel server and windows authentication mode if I click OK at that point I'll then be informed that I need to restart my service essentially to make these changes take effect so I'm going to click OK I'm going to disconnect from the server I'll need to reconnect to it anyway and then I'm going to go back to the Start menu and choose to open up sequel server configuration manager in here I can right click on my sequel 2016 training instance and then choose to restart it and once the progress bars have done their thing and stopped the service and then restarted it I can go back to sequel server manager to do I'm going to leave sequel server configuration manager open for the time being we'll probably come back here before the end of the video to restart the service again but now that that's happened I should be able to switch back to sequel server management studio first of all I'm going to connect using my Windows credentials so I'm going to connect to the database engine using Windows authentication to connect as my system administrator role and then I'm also going to choose to connect again to the same database engine but this time using sequel server authentication so I've got test login entered as log in then I can enter my password and then once I've done all of that and I can click the connect button and now I connected as my test login now even though I'm connected as this test login I'm not going to be able to do much with the items in the server at this point if I choose to expand the databases folder I'll see the databases that exist but there won't be much that I can do with them so for instance if I tried to open up the test DB database it tries to expand but then eventually what's going to happen is I'll be presented with an error message at some point in the near future hopefully there we go so the database isn't accessible to me the reason for that is I've not been added as a user to that database with this particular login so as I'm logged in as both system administrator and this test login I can change things in one instance one connection and then have those effects the second connection so let's have a quick look I want to make sure that I'm added as a user to the test DB database now there are three main elements associated with creating users in a database they're all contained in the same folder within the database so start by expanding your test DB and then you can expand the security folder in there and the first three folders that appear uses roles and schemas are the three main things you need to know about now a user store at the database level has to be connected to a log installed of the server level so if I expand the security folder at the server level and then the logins falter in there every login can have a single user created in each database so essentially the login is mapped to a user in a database you can map the same login to a different user in as many different databases as you'd like and for that reason if you find that you're creating the same user in lots lots of different databases it might be a good idea to create users in the model database instead so the model database as you remember is the template for all new databases has a security folder and the user's folder in there we're not going to do this in the model database we're going to create our new user in the test DB database if you expand the users folder of the database you'll see that it already contains several items and the most important one of these is dpo or the database owner user now DPO is essentially just an alias for the default system administrator account or login so if I were to right-click on the DPO user and choose to view properties you'll see the dialog box tells me that although this user is called dbo in this database the actual login name from which it's mapped is the same one as my system administrator user account or suss wise I'll slash Andrew gold so essentially that tells you that any login can be represented as any different username in a database but as I said before you can only map one login once per database so if I were to just cancel from this dialer box and I were to try to create a new user based on that same login then that would fail now what a user is allowed to do in a database is determined by which permissions it's been assigned and you can assign permissions to a user in a couple of different ways you can go about this process in a quite long-winded fashion and assign individual explicit permissions to a user alternatively and slightly more efficiently you can use database roles so these are similar to the server roles we looked at earlier on for logins a database has got database roles so if you look in the roles folder there's a database roles folder in there and again the names of these provide you with some clue as to what these roles allow the DP owner database role is almost the equivalent I suppose of the system administrator role for the server so DP owner allows you to do anything you like to the database so if I can just again take you to the properties dialog box for the dbo user and head on to the membership page this shows you which roles this user is a member of so you'll see that this one is assigned to or as a member of the DP owner role meaning that dbo can essentially do anything it wants to this entire database including deleting it as it turns out the final piece of the user puzzle is something called a schema and you'll see the schemas folder sits just below the roles folder in the security vault of the database just while I still have this properties dialog box displayed for the DPO user you'll see that it also has an owned schemas page so each schema that exists and a schema is essentially just a way to organizing grant permissions to various items in the database each schema must be owned by something in the database so you can see that my dbo user doesn't actually own any schemas because there are no checked boxes it doesn't even own the DB owner schema something just cancel out of this dialog box and then let's have a look in the schemas folder itself so if I expand the schemas folder and then have a look at the names of the items in there we'll find that there's a schema in there called DB owner if you want to know which item in the database does own this schema you can right click on it and then choose properties and on the general page of the dialog box it will tell you which item owns this schema so it's so the DB owner schema is also owned by something called DB owner now what this actually refers to it's not as circular as it seems this this DB owner refers to the database for all DB owner so a schema can be owned by both a database role or a user in the database now we'll come back and explain a little bit more in detail about schemas in just a moment but all three items are important users roles and schemas if you're going to create users in database so let's just cancel this dialog box and let's actually create a new user associated with our test login from our server hopefully as you've come to expect by now in sequel server management studio to create a new item you can simply right-click on the appropriate folder and choose the top option so in this case right click on users and choose new user that'll give you a dialog box then that allows you to configure the new user the first thing you have to specify is what type of user you're creating in this case we're specifying is a sequel user with a login so this is associated with a login in the logins folder of the which I've just just managed to hide at the bottom of the screen there so a login at the security folder at the level of the server there's an option that says sequel user without a login so it is possible to create a sequel user that's confined just to this database and doesn't correspond to any login in the syrup in the logins folder at the server level you can only do that however if you set up the database to be contained and I mentioned that property just earlier on when we created our new database we haven't created a contained database we can't do this we can't choose a sequel user without a login we can also choose various other options including the windows user so if we were using Windows authentication only we could map this to a Windows user but in this case we're going to choose sequel user with login the next thing I need to do is specify a username and as we saw before the username doesn't necessarily need to correspond to the name of the login so that's quite a user in this case I'm going to call it test user following my sensible boring naming convention from all the other items I've created so I've got test user then I need to associate it with a login so in this case I could either type in the login name or more conveniently I can select it by clicking the ellipsis button and then browsing for all the logins available to this server so I scroll down I'll find my test login and I can check that box click OK and then click OK again the final thing I can do is associate the the default schema to this user as well so the default schema determines if this user creates any objects what schema those objects will belong to by default I don't have to specify anything here I can choose one of the existing schemas by clicking the Browse button and then browse again to select one I can even make up a brand new schema name here that doesn't even exist yet so in fact that's what I'm going to do I'm going to create a new schema guess what test schemer at least I'm consistent if nothing else you can then move on to the own schemas page and here you can specify the ownership of any schemas that belong to the database now the only schema that I'd like this user to own is the one that we haven't yet created the one called test schema we've references name even though it doesn't yet exist yeah so if I go back to the on schemas page there are no there's no schemas I want this user to open so then let's move on to the membership page and this lets me assign the user to any of the built in database roles so what I'd like this user to be able to do I'd like them to be able to read any data from any table so I can you can do assign them to the data reader role I'd also like them to be able to modify any data in those tables so I'm going to sign them to the data writer role I'd also like this particular user to be allowed to create and alter any existing objects in the database so I'm going to assign them to the DDL admin role to DDL data definition language so that allows me to create and alter objects if I wanted to I could also head onto the secure doubles page here and here what I can do is assign individual explicit permissions for separate objects so the way this page works is I can head onto the search box at the top and I can choose to create or look for specific objects in the dialog box and select object types and then I've got a great big long list of different types of objects I can select so for instance if I looked at these stored procedures and clicked ok and then hit the Browse button to browse for store procedures that belong to this database there's a whole bunch of systems store procedures so I could select as many or as few of these as I liked when I clicked ok and ok again that will be added to my list of securable so securable ZAR objects in the database that can have security assigned to them if that made sense and then for each item in this list I can choose exactly what this user is allowed to do to it so you can imagine how time-consuming a process that is for individual items and individual users and individual explicit permissions so I'm not actually gonna bother assigning anything here I'm not going to change any of these settings for this single store procedure at this point all I'm going to do is click the ok button to create this basic user who's called test user with mapped to the test login stored in the logins folder of the server creating or having a default schema of test schema which doesn't yet exist and is a member of three separate database roles data read a data writer and DDL admin so I click OK at this point that new user will have been created ok so at this point let's just tidy up the window a little bit I'm going to collapse the schemas folder and the roles folder and I'm also going to collapse the security folder for the server and then what I'm going to try to do is connect to this database so I choose if I choose to refresh this server that was logged into by my test user my sort of my test login if I just refresh this and then expand the databases folder again and again that might take a little while for that to happen but eventually when we get there I can expand the test DB folder and this time I'll find but I can actually access it one final thing that we're going to do is make sure that the schema that we assigned as the test users default schema inventively called test schema actually exists because currently it doesn't so if I expand the schemas folder and then right-click on that and choose new schema we're going to create a new schema name called test schema or try to call it to test a schema there we go the the owner of the schema I'm going to set the owner of the schema to be the test user so I click the search button click browse and then scroll through you'll see I can pick from a range of database roles and users so I'm going to choose my test user by checking the box and clicking ok and clicking ok again it is also possible to use a schema to assign permissions to both users and database roles if I go into the permissions page and then click the search button at the top I can search for users database roles and application roles I click the Browse button so fairly limited set of options in here just because I've not added that many additional users and roles to the database of course but I could choose a user here and then in the same way as previously in fact let me just very quickly demonstrate this Pelley point is choosing the test users that's the owner for the schema anyway but I could then choose what missions are granted to that user I'm not going to do that in this case in fact in this case I'm not going to take any of these boxes I'm just going to click the ok button so having not checked any boxes I haven't actually modified any of the permission settings if I click OK that will be sufficient to create the test schema that's tied in now with the test user user if I view the properties the default if I go back there on the general page I've got the default schema as test schema I'm also going to make this test user the owner of that schema which you can see has already been done as it turns out so if I click OK and then just collapse up these folders again just to tidy up a little bit we're basically done in terms of creating users in the database so let's have a look at a couple of the very basic implications of what we've done here so far so currently we've got two connections to the same sequel server using two separate logins so we've got the default system administrator login which in this case is me and we've also got our new test login that we created in this video now we've already established that the system administrator login can do whatever it wants to the server including deleting databases and creating new ones from scratch we saw earlier on that the test login which has only been assigned to the public server role can't even connect to a database unless it's been mapped to a user in that database so of course I can't create databases or delete them all I should be able to do with a test log in and test user is according to the permissions that I've assigned to the test user in that database now because I've assigned the test user to the DDL admin role that should enable me to create tables in the database so let's give that a quick try I'm going to expand the tables folder and then right click on tables and choose new table I'm going to use a table designer to do this just for simplicity I'll get a little warning that I'm not logged in as a database owner or administrator so I might not be able to save changes but because I've assigned this user to the DDL admin role I know that I will be allowed to do that so I click OK that'll give me the basic table designer I just also like to display the properties window here as well as I'm going to head to the View menu and choose properties window just to demonstrate that the default schema that this table will be created in is the test schema the on the we created earlier on we're going to talk a lot more about table design in the next video in the series so I'm not going to go into any detail here whatsoever other than to create a simple column name which I'm going to call oh let's call it test column one and then I'm going to use the default data type which I'm not going to explain anything about at this point and then I should just be able to close and save this and I'll give it I'll just leave it with a default name of table one and it will be created in the test schema so if I click OK that table should now exist so i refresh my tables folder with the test login login and then do the same thing I might not need to do this for the system administrator I can just expand the tables folder and that table is viewed there as well I can also create tables as the system administrator in this case of the DPO database user which is mapped to the system administrator of course so if I right click on the tables folder in this connection and choose new table we will see this time the default schema assigned to this table is DB ohm I can actually change this I should have mentioned this previously I can change the the schema that the table belongs to when I create it or indeed any object but in this case I'm going to leave it in the DB o schema so it's called this one again I'll call it test column two and again I'll use the default beta type and then choose to close down that table choose yes to save its changes and I can call it table 1 again because this bit long will belong to a different schema the table is allowed to have the same name because its scope is different so I'm going to click OK I've got another table 1 and again if i refresh the tables folder and I'll do that again for the test login connection as well I've now got tables in two different schemas just to demonstrate that we can affect what this user is allowed to do I'm going to go back to the system administrator connection and then expand the security folder and then I'm going to expand users and then right click on test user and choose properties I'm then going to go to the membership page and I'm going to remove its membership from the DDL admin database role when I click OK this time then collapse the security folder again I if I try to go back to the tables folder and I try to do anything now in terms of creating tables so I can right click on tables and choose new table I'll get the same warning as previously that I might not be able to save change it but I'm going to click OK and ignore it again I'm going to call my new column test column 3 and then I'll use the same data type as previously it should be belong to the test schema schema and then if I choose to close the table down and choose yes to save the changes click OK to call it table 2 this time I'll be prevented completely saying that I don't have create table missions so I'm going to click no I don't want to continue attempting to save the table and that will close it down the removal from the DDL admin role also means that I can't delete objects from the database so if I try to delete the table one table that belongs to the dbo schema I'll find that I won't be allowed to do that if I click OK in the dialog box I'm prevented from doing so because I don't have permission if I try to do this however for the table which belongs to the test schema schema because the test user is the owner of the test schema it means that implicitly that user also owns any object that belongs to that schema so if I try to delete table 1 from the test schema in this case I'll find that I can do that I can create more elaborate arrangements of permissions and ownership of various objects and just to maybe very quickly demonstrate just to wrap up some of the things you can do what I'm going to do is head back to the administrator connection and I'm going to create a brand new table that belongs to the test schema schema so I'm going to write clean tables choosing you and choose table I'll give this column a I'll give it a really random name just the first few keys I press in the keyboard just to give it a name and it's called a data type there as well I'm going to change the schema from dbo to test schema and then I'm going to choose to close this down and choose yes to save the changes and that will create the new table table - if i refresh the tables folder in the test schema and that will of course belong or be visible to both connections what I'm now going to do is change ownership of the test schema schema so if I head back to the security folder in the system administrator connection and then go to the schemas folder and then I'm going to right click on test schema and choose properties the first thing we're going to do is change the ownership so rather than use test user as the owner I'm going to browse for DB ohm so remember that's just an essentially an alias for my system administrator account just by click OK to update that and then click OK again what I'm now going to do is use a test schema to modify the permissions for objects in the test schema for the test user so if I right click test schema and use properties and then click I'm going to search for the test user user so I'm going to browse for test user then click OK and then click OK again now currently the test user isn't allowed to create or delete or alter objects and data is because we've removed to test the user from the DDL admin and database role so what I'm going to do is I'm going to grant permission to the test user for items in the test schema to alter them and actually I think that's probably it that's all the ones you don't want it to be able to delete objects in there I could allow it to insert data into there but I can do that anyway because the the test user is a member of the the data reader and data writer role so I don't need to do that let's just then at that point with the alter statement written if I click ok and then scroll back down again into the test login connection so just to establish first that I'm not allowed to make changes to normal tables or ones that don't belong to the test schema schema if I try to right click on table one that belongs to the dbo schema and then choose design and then click ok just to confirm that I want to attempt to do that you'll see that in this case table one sets of read-only so I don't have enough lights to make changes to that table so I can close down at that table what I can do because I have permission of the alter permission for items in the test schema if I right click on this table and choose design I can now get the same little warning but I can click OK and you'll see here I am allowed to make changes so I can create a new column with an equally useless name but that's not important to this stage then if I choose to close a table down and click yes to say the changes click yes anyway just to confirm that I want to do that and you'll see that this table now has that new column created as well so there's a brief little flavor for what you can do with users roles and schemas okay well that's basically the end of this video apart from a little bit of tidying up to reset things back to the way they were before we began so I'm just going to disconnect from my test login connection so I'm going to right click and choose disconnect and then I'm going to view the properties of my system administrator connections going to right click on that and choose properties what I'm going to do then is head on to the security page and if you remember we reset the server authentication back to sequel server and windows authentication earlier on so going to reset it back to windows authentication mode only and then click OK and I get a little warning about that not having any effect until I've restarted the server so to do that I'm going to go back to sequel server configuration manager then I can right click on sequel server and choose restart making sure it's the correct instance of course it was sequel 2016 training and then a couple of very last things I'm going to do just to finish off tidying up is I'm going to delete the database and the extra login that I created earlier on so when the service is restarted I ought to be able to head back to sequel server management studio and then I can restart the service by oh it's restarted itself there it goes and then if I can go back to the databases folder first of all I can right click on test a DB and choose delete I can then click OK to confirm that and then finally I can go into the security folder go into logins find my test login right click and delete that as well once I've clicked ok and clicked ok again that's everything reset back to its original state so that's the end of the video hope you've enjoyed that one and you've got a bit more of a feel for how basic security works in the sequel server what we'll do in the next video is move on and start talking about how to create tables and a bit of an interesting discussion about the various different data types for columns in tables thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 120,028

Rating: 4.9182086 out of 5

Keywords: sql server, database, schemas, permissions, database roles, server roels, logins, users, roles, wise owl

Id: IDxXUBNBxPA

Length: 44min 45sec (2685 seconds)

Published: Wed Nov 02 2016