Now, a user is a database level security principle.
Logins must be mapped to a database user in order to connect to a database, the user refers to an account
in Mouskouri server database, which is then used to access the database.
Now, users can be created in a few different ways, graphically with a query or while user mapping
a log in.
So here to create a user, I'm just going to use a user command.
And we should specify a log in to be mapped to the user.
Now, each database has a security folder and a security folder will contain the user's folder, which
is containing user's.
And a security folder is a database level folder.
So let's create a log in with user mapping.
Just right, click on the server level security folder and choose new login from that list.
And the name of our login will be admen, and it will connect with escarole server authentication.
Leave the informal setting as Jack.
From the server roll, the public role is checked.
From user mapping page, we can see that the list of databases which are in the instance.
Now, let's say that we want to create a user for the adventure works, a database, so we're going
to select it from the list below and make sure that only public role data, reader rule, which will
allow that user to be able to issue a select statement against all tables and they can view it in the
database.
The data writer role that will give implicit access to tables and views within a database and DDL admin
role that's going to allow a user to create, drop or modify any objects within a database.
So just make sure the right one is selected.
And click OK to save it.
And here we can see the admin log in from the server level folder and the admin user from the database
level folder.
Now, we can also create a user by just right clicking on the user's folder in the database level holder.
We can define the name of the user and let's call it test underscore user.
Then we should define a login for this user, and lastly, it's optional that we can define a schema.
Now, if we leave it blank, the DBO schema will be saved by default.
And we can save it just by clicking, OK, but we have already created a user in that log in, so it's
going to let you cancel it.
So now the last way to create a user is by using a query.
So the user command is used to create a user.
And we should specify the username, so the name of the user will be test underscore user and just like
in the last case, each user is created for login.
So we should specify the name of the login.
And we want to create test underscore yuzu for the test, underscore log in, log in, and if we execute
the query, the user will be created in the user list.
But we have already created a user for test on score log.
And so it will not allow us to create more than one user in a log in.
That's useful information.
And that is it, we have learned the three different ways to create users in a specific database.
User permissions.
The permissions are the rights to access the database objects.
Commissions can be granted to a user or a role to allow that user a role to perform operations such
as selection, insertion or modification of data Rose.
Each database object has an owner, so by default, the owner is the creator of an object by the ownership
can be transferred later on after the object has been created.
And in addition to the owner, the members of this has admin fixed server roles, have full permissions
on all objects in all user and system databases.
Now, there's also a public role and the public role is a special database role to which each database
user belongs.
The public role contains default access permissions for any user who can access the database.
This database role cannot be dropped, but it is strongly recommended not to grant superfluous permissions
into the public role because each database user has the public roles permissions.
Yet it.
Before starting to manage the user permissions, let's log in with our test log in that has recently
been created.
Now click disconnect and then click connect to establish a new connection.
Make sure ask you all server authentication is selected and username is admin and type the password.
OK, so the Log-in failed because the default settings of the Escorial server does not allow escarole
server authentication connection, so let's log in with Yaara as the user and solve this problem in
two steps.
The first step is unable to ask you all server authentication.
Just right, click on the instance from the Object Explorer and click the properties from the list under
the security page and then select Escorial Server and Windows authentication mode and click OK to save.
So I ask you, all servers warning us to restart Escorial server service.
This one is the second step.
So perhaps the Windows key from your keyboard and type Escorial server and you will see the actual server
2019 configuration manager.
That's right, click on the actual server service and choose restart from the opening window.
An awesome, selfless service has been restarted just like that.
All right, so that's it, we will be able to log in with our admin log in.
Now, remember that we have enforced the user to change the password at the first log in, right.
So we'll set a new password.
Congratulations, we have connected to ask you all server with a new login.
Now, remember that we have created the admin user with the adventure works database privileges, so
in this case, we should not be able to have permissions for the other databases.
Right.
We'll be able to see folders and tables in the Adventure Works database.
So we need to connect as a sys admin again to manage the permissions of the admin user.
So just right click on the admin user under the security folder in the Adventure Works Database.
Uncheck all database rolls under the membership page and then select the person schema from the own
schema page.
So let's connect with the admin log in and see what's changed.
Oh, and by the way.
We can create multiple connections in the same instance.
So just click on the Connect option near the Connect Disconnected Options.
Jews database engine and log in with admin.
Awesome.
So now we have two connections in the same instant.
One is Yaara, which is the sys admin and the other one is the admin log in.
Now, if we try to list tables in the Adventure Works database from the admin log in.
We will see the table is only referenced by the person schema.
So remember that we have checked the person schema from a list, right?
So to remove the person schema from the user privileges.
Find the schema folder under the database level security folder and right, click on the person schema,
change the schema owner to DBO from the popup window.
And we can check it from the user properties, papà.
You should see the person schema is unchecked under the old scheme as page.
And while we're here, let's check the data reader, data writer and DDL admin role is again.
Now we'll be able to create a table in the database from the admin log in.
So let's try to create a test table, just call it table one.
Never mind about the warnings.
Here's our table.
Now, in the next section, we're going to learn how to set user permissions to work on a database with
commands.
So that means we'll see you in the next section.