1 00:00:00,150 --> 00:00:03,300 Now, a user is a database level security principle. 2 00:00:04,400 --> 00:00:11,810 Logins must be mapped to a database user in order to connect to a database, the user refers to an account 3 00:00:11,810 --> 00:00:17,060 in Mouskouri server database, which is then used to access the database. 4 00:00:18,260 --> 00:00:24,290 Now, users can be created in a few different ways, graphically with a query or while user mapping 5 00:00:24,290 --> 00:00:24,800 a log in. 6 00:00:26,460 --> 00:00:30,180 So here to create a user, I'm just going to use a user command. 7 00:00:31,190 --> 00:00:34,310 And we should specify a log in to be mapped to the user. 8 00:00:36,350 --> 00:00:44,000 Now, each database has a security folder and a security folder will contain the user's folder, which 9 00:00:44,000 --> 00:00:45,590 is containing user's. 10 00:00:46,640 --> 00:00:49,670 And a security folder is a database level folder. 11 00:00:51,130 --> 00:00:53,440 So let's create a log in with user mapping. 12 00:00:54,690 --> 00:01:00,330 Just right, click on the server level security folder and choose new login from that list. 13 00:01:01,430 --> 00:01:07,220 And the name of our login will be admen, and it will connect with escarole server authentication. 14 00:01:08,720 --> 00:01:10,470 Leave the informal setting as Jack. 15 00:01:13,270 --> 00:01:16,090 From the server roll, the public role is checked. 16 00:01:19,150 --> 00:01:25,990 From user mapping page, we can see that the list of databases which are in the instance. 17 00:01:28,260 --> 00:01:33,210 Now, let's say that we want to create a user for the adventure works, a database, so we're going 18 00:01:33,210 --> 00:01:42,570 to select it from the list below and make sure that only public role data, reader rule, which will 19 00:01:42,570 --> 00:01:50,340 allow that user to be able to issue a select statement against all tables and they can view it in the 20 00:01:50,340 --> 00:01:51,030 database. 21 00:01:52,750 --> 00:02:01,480 The data writer role that will give implicit access to tables and views within a database and DDL admin 22 00:02:01,480 --> 00:02:08,090 role that's going to allow a user to create, drop or modify any objects within a database. 23 00:02:08,620 --> 00:02:11,230 So just make sure the right one is selected. 24 00:02:12,670 --> 00:02:14,140 And click OK to save it. 25 00:02:16,720 --> 00:02:22,930 And here we can see the admin log in from the server level folder and the admin user from the database 26 00:02:22,930 --> 00:02:23,650 level folder. 27 00:02:28,030 --> 00:02:33,640 Now, we can also create a user by just right clicking on the user's folder in the database level holder. 28 00:02:35,730 --> 00:02:40,230 We can define the name of the user and let's call it test underscore user. 29 00:02:41,520 --> 00:02:50,280 Then we should define a login for this user, and lastly, it's optional that we can define a schema. 30 00:02:52,910 --> 00:02:57,470 Now, if we leave it blank, the DBO schema will be saved by default. 31 00:02:59,070 --> 00:03:06,420 And we can save it just by clicking, OK, but we have already created a user in that log in, so it's 32 00:03:06,420 --> 00:03:07,500 going to let you cancel it. 33 00:03:11,120 --> 00:03:15,050 So now the last way to create a user is by using a query. 34 00:03:16,930 --> 00:03:19,930 So the user command is used to create a user. 35 00:03:21,230 --> 00:03:29,120 And we should specify the username, so the name of the user will be test underscore user and just like 36 00:03:29,450 --> 00:03:32,780 in the last case, each user is created for login. 37 00:03:32,780 --> 00:03:35,570 So we should specify the name of the login. 38 00:03:36,480 --> 00:03:44,910 And we want to create test underscore yuzu for the test, underscore log in, log in, and if we execute 39 00:03:44,910 --> 00:03:47,550 the query, the user will be created in the user list. 40 00:03:47,550 --> 00:03:51,090 But we have already created a user for test on score log. 41 00:03:51,090 --> 00:03:55,770 And so it will not allow us to create more than one user in a log in. 42 00:03:56,080 --> 00:03:57,330 That's useful information. 43 00:03:58,940 --> 00:04:06,410 And that is it, we have learned the three different ways to create users in a specific database. 44 00:04:07,680 --> 00:04:08,940 User permissions. 45 00:04:10,930 --> 00:04:15,340 The permissions are the rights to access the database objects. 46 00:04:16,330 --> 00:04:23,380 Commissions can be granted to a user or a role to allow that user a role to perform operations such 47 00:04:23,380 --> 00:04:27,400 as selection, insertion or modification of data Rose. 48 00:04:29,610 --> 00:04:37,710 Each database object has an owner, so by default, the owner is the creator of an object by the ownership 49 00:04:37,710 --> 00:04:42,120 can be transferred later on after the object has been created. 50 00:04:43,520 --> 00:04:49,910 And in addition to the owner, the members of this has admin fixed server roles, have full permissions 51 00:04:50,120 --> 00:04:54,680 on all objects in all user and system databases. 52 00:04:58,260 --> 00:05:05,760 Now, there's also a public role and the public role is a special database role to which each database 53 00:05:05,760 --> 00:05:06,750 user belongs. 54 00:05:07,710 --> 00:05:13,830 The public role contains default access permissions for any user who can access the database. 55 00:05:14,890 --> 00:05:22,390 This database role cannot be dropped, but it is strongly recommended not to grant superfluous permissions 56 00:05:22,390 --> 00:05:28,750 into the public role because each database user has the public roles permissions. 57 00:05:29,710 --> 00:05:30,280 Yet it. 58 00:05:35,180 --> 00:05:41,930 Before starting to manage the user permissions, let's log in with our test log in that has recently 59 00:05:41,930 --> 00:05:42,590 been created. 60 00:05:43,820 --> 00:05:49,130 Now click disconnect and then click connect to establish a new connection. 61 00:05:50,530 --> 00:05:56,290 Make sure ask you all server authentication is selected and username is admin and type the password. 62 00:05:57,310 --> 00:06:05,440 OK, so the Log-in failed because the default settings of the Escorial server does not allow escarole 63 00:06:05,440 --> 00:06:14,020 server authentication connection, so let's log in with Yaara as the user and solve this problem in 64 00:06:14,020 --> 00:06:14,950 two steps. 65 00:06:17,520 --> 00:06:21,030 The first step is unable to ask you all server authentication. 66 00:06:22,380 --> 00:06:29,010 Just right, click on the instance from the Object Explorer and click the properties from the list under 67 00:06:29,010 --> 00:06:35,850 the security page and then select Escorial Server and Windows authentication mode and click OK to save. 68 00:06:37,010 --> 00:06:40,940 So I ask you, all servers warning us to restart Escorial server service. 69 00:06:41,920 --> 00:06:43,600 This one is the second step. 70 00:06:45,870 --> 00:06:51,510 So perhaps the Windows key from your keyboard and type Escorial server and you will see the actual server 71 00:06:51,510 --> 00:06:53,760 2019 configuration manager. 72 00:06:55,320 --> 00:07:00,420 That's right, click on the actual server service and choose restart from the opening window. 73 00:07:02,130 --> 00:07:05,280 An awesome, selfless service has been restarted just like that. 74 00:07:06,720 --> 00:07:10,570 All right, so that's it, we will be able to log in with our admin log in. 75 00:07:11,490 --> 00:07:16,860 Now, remember that we have enforced the user to change the password at the first log in, right. 76 00:07:17,920 --> 00:07:19,390 So we'll set a new password. 77 00:07:26,480 --> 00:07:30,530 Congratulations, we have connected to ask you all server with a new login. 78 00:07:31,890 --> 00:07:38,580 Now, remember that we have created the admin user with the adventure works database privileges, so 79 00:07:38,580 --> 00:07:43,260 in this case, we should not be able to have permissions for the other databases. 80 00:07:43,260 --> 00:07:43,550 Right. 81 00:07:44,630 --> 00:07:49,010 We'll be able to see folders and tables in the Adventure Works database. 82 00:07:51,750 --> 00:07:57,750 So we need to connect as a sys admin again to manage the permissions of the admin user. 83 00:08:11,580 --> 00:08:16,670 So just right click on the admin user under the security folder in the Adventure Works Database. 84 00:08:18,740 --> 00:08:25,940 Uncheck all database rolls under the membership page and then select the person schema from the own 85 00:08:25,940 --> 00:08:26,720 schema page. 86 00:08:28,240 --> 00:08:32,050 So let's connect with the admin log in and see what's changed. 87 00:08:33,460 --> 00:08:34,570 Oh, and by the way. 88 00:08:35,530 --> 00:08:38,510 We can create multiple connections in the same instance. 89 00:08:39,430 --> 00:08:43,710 So just click on the Connect option near the Connect Disconnected Options. 90 00:08:44,960 --> 00:08:48,080 Jews database engine and log in with admin. 91 00:08:53,950 --> 00:08:54,590 Awesome. 92 00:08:55,510 --> 00:08:58,600 So now we have two connections in the same instant. 93 00:08:59,720 --> 00:09:05,750 One is Yaara, which is the sys admin and the other one is the admin log in. 94 00:09:07,250 --> 00:09:12,350 Now, if we try to list tables in the Adventure Works database from the admin log in. 95 00:09:13,760 --> 00:09:18,380 We will see the table is only referenced by the person schema. 96 00:09:20,890 --> 00:09:24,260 So remember that we have checked the person schema from a list, right? 97 00:09:24,730 --> 00:09:28,450 So to remove the person schema from the user privileges. 98 00:09:31,900 --> 00:09:37,900 Find the schema folder under the database level security folder and right, click on the person schema, 99 00:09:38,620 --> 00:09:41,950 change the schema owner to DBO from the popup window. 100 00:09:44,800 --> 00:09:47,260 And we can check it from the user properties, papà. 101 00:09:48,540 --> 00:09:53,100 You should see the person schema is unchecked under the old scheme as page. 102 00:09:54,090 --> 00:10:00,900 And while we're here, let's check the data reader, data writer and DDL admin role is again. 103 00:10:14,310 --> 00:10:19,320 Now we'll be able to create a table in the database from the admin log in. 104 00:10:21,790 --> 00:10:25,150 So let's try to create a test table, just call it table one. 105 00:10:36,880 --> 00:10:38,190 Never mind about the warnings. 106 00:10:45,180 --> 00:10:46,080 Here's our table. 107 00:10:47,780 --> 00:10:52,820 Now, in the next section, we're going to learn how to set user permissions to work on a database with 108 00:10:52,820 --> 00:10:53,510 commands. 109 00:10:54,660 --> 00:10:57,270 So that means we'll see you in the next section.