YT YouTube2Text

YouTube2Text → Transcripts → Curso SqlServer #2 Credenciales (Login) y Usuarios (Permiso a base de datos)

Curso SqlServer #2 Credenciales (Login) y Usuarios (Permiso a base de datos) — Transcript

by Multiverso TI · 2,755 words · 391 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:01Welcome to a new video. We are in the
0:03SQL Server course. This is our second
0:06lesson. In this lesson, we will learn a
0:10bit more about creating users, creating
0:14logins, and also assigning roles and
0:17profiles. In the previous lesson, we
0:22had already created a login, right?
0:26Access to the system. Here it is. We
0:31had created the system access with the
0:34name Williams. Now we are going to go a
0:37little bit deeper. So, I am already
0:40logged into SQL Server. System accesses
0:46, or logins, are found within the
0:49security folder, inside the logins
0:52folder. All the system accesses are
0:56located here, right? All the logins. I
1:00am making a distinction between logins
1:04and users because in SQL Server, logins
1:07are not the same as users. So, what we
1:11see here in the login folder are the
1:15credentials to access the server, right
1:19? They are the credentials to access
1:21SQL Server. They are users for the
1:26entire database in general, but then
1:29there are other users that SQL Server
1:33creates to assign to a specific
1:36database so that they cannot have
1:40access to other databases and other
1:44schemas, right? So, we are going to
1:48learn both ways right now. First, we
1:50will learn how to create logins from
1:53the login folder. This is very easy,
1:56right? Just by right-clicking, New
1:59Login. Here the system will ask us for
2:03the login name. The login is the
2:06credential that we will use to access,
2:09right, with Management Studio. So,
2:11let's put a name here, for example.
2:13Let's use Carlos, for example. And when
2:18creating the login, we can decide
2:20whether to create it with Windows
2:22Authentication, that is, without a
2:24password, or with SQL Server
2:26Authentication. So, I will use it this
2:30time. I recommend that you set a
2:31password. Let's put a simple one, for
2:33example, 1 2 3 4. And if we click okay,
2:39the user will be created automatically,
2:41but it will be created with certain
2:43restrictions, right? For example, it
2:46won't have access to tables or access
2:48to the databases because it doesn't
2:50have the credentials. To assign the
2:54credentials, we can go here to where it
2:57says server roles and here we must
2:59assign a role. Usually, so that we
3:04don't have any issues accessing all the
3:06databases, right? We assign it the
3:09sysadmin role. It is the same role that
3:13SQL Server assigns to the SA user,
3:16which is the user created during the
3:18database installation. This way, this
3:22user could access the entire database
3:25and all objects in the database, right?
3:30Another thing to keep in mind when
3:33creating the login is whether we want
3:36their password to expire or have
3:38certain characteristics, complying with
3:41a specific SQL Server policy. I
3:45recommend that you remove all of that
3:47so you don't run into problems, because
3:50all this user management is handled
3:52from the front-end side with a
3:54programming language like Visual Basic,
3:57C#, or Java. So, we don't do this with
4:00the database connection user, so I
4:02recommend you remove all this password
4:05expiration stuff. So, we click here
4:08where it says "enforce password policy,
4:12" right? We are telling it not to
4:16enforce password policies for this
4:19login. So, I click okay. And look here
4:23on the left inside my login folder, my
4:25new login has already been created. Uh,
4:28Carlos, right? Carlos. The other way to
4:31create it, I could already log in with
4:33this user. The other way to create it
4:35is through code. It could be, for
4:38example, here "create login" followed
4:41by the name. of the login. In this case
4:45, for example, I'm going to create a
4:47female one, let's say, for example, "
4:51create login." And to assign the
4:54password, I say "password" and here I
5:00put the password. Likewise, for example
5:02, I will also put 1 2 3 4. Done. With
5:08this line of code, I would already be
5:11creating a new login with the password
5:141 2 3 4. Just like I just created the
5:17Carlos login from the explorer window,
5:20right? Now, if I want to set the
5:23password here, it would be with a comma
5:26here. I want to remove the password
5:30expiration, I mean. So, it would be
5:32here "check." If you notice, I'm going
5:39to open Carlos here. When I remove the
5:42password policy, it also automatically
5:48removed the password expiration. So
5:52what I have to remove is the password
5:54policy, so it would be "check." policy
5:58and I say equals off. With this, my
6:05password would no longer expire for
6:07this new login, uh, Judit. I select all
6:11this and execute it. If I now refresh
6:15my login folder, notice that my new
6:18login Judit appears here. Now the only
6:21thing we would be missing, if I
6:23double-click here on the login Judit,
6:26you will notice it doesn't have the
6:28administrator role, right? So we can do
6:31that with an additional line of code.
6:35Now, this is actually very simple. If
6:38you don't remember that line of code,
6:40it isn't done when creating the login,
6:42but rather afterward by altering the
6:44login. So, I'll leave you here a way to
6:48know that line of code, right? For
6:50example, the user, the user Carlos that
6:52we just created does have it. So, if I
6:55double-click on the user Carlos, notice
6:57that this one does have the sysadmin
7:00role. How can I find out that line of
7:02code to assign the sysadmin role? Very
7:05easy. I right-click on the Carlos login
7:09and I tell it, for example, here "
7:12script login as" and I select the
7:14option that says "create" so it shows
7:17me what code was used to create, to
7:20assign the admin role to the user.
7:23Carlos, notice that here is the line of
7:25code that was used. I can copy this.
7:28Now I close this. I'm not interested in
7:30this anymore. And now I am simply
7:33altering the sysadmin role and I am
7:35assigning it the login that will also
7:38have the sysadmin role. In this case,
7:41the login will be the one I just
7:42created, which is Judit. Done. So, the
7:46statement is alter server role sysadmin
7:51add member and the name of the login to
7:54which I want to assign the
7:57administrator role. I select all this,
8:01execute, and now if I refresh my login
8:03folder here, if I view my login Judit,
8:06notice that now it has the sysadmin
8:09role. So, in this way, we have just
8:13created with code the same thing we
8:16created for the user through the Object
8:19Explorer. So, I'm going to put some
8:22comments here because I'm going to put
8:23this in the video description. This is
8:26login creation and this is role
8:34assignment to the login. Okay. And now
8:39there is one more statement here in in
8:43SQL Server which is user creation.
8:47There is a statement to create a user,
8:49it is the create user. Next, we have to
8:54indicate the name of the user. So, for
8:58example, I am going to create the user
9:00Alberto, and we have to indicate to the
9:03user, uh, what credential they will use
9:06to access the system, right? You don't
9:10enter a password for the user; you
9:12enter a login that was previously
9:15created, and this login is the one that
9:18has the user and the password, right?
9:21So it can be one of those we have
9:22already created. For example, we have
9:24created Carlos, we have created Judit,
9:26and it could be one of these two. We
9:28cannot assign a password directly to
9:30the user. Therefore, the user uses a
9:33created login. So, let's create a new
9:37login here quickly, also called Alberto
9:41, right? Down here, I'm going to tell
9:45it: create a login called Alberto, let
9:47its password be 1 2 3 4, and let its...
9:52let its password not expire. I execute.
9:55Done. If I refresh my logins folder,
9:58there is my login, Alberto. Now I am
10:01going to create my user Alberto, who
10:03will use the Alberto login. I select
10:06this line of code and execute it.
10:11That's it. I could have given it
10:15another name, but, uh, I want to make a
10:17distinction. A login is one thing, and
10:19a user is another. The login is the set
10:23of credentials to access. the system,
10:27the entire SQL Server environment, and
10:29they are stored inside this, uh, login
10:31folder. Users are not assigned, they
10:35are not stored inside the login folder.
10:38Users are assigned directly to a
10:41specific database. In this case, since
10:44I am, uh, selected on the master
10:47database, this user login has been
10:49assigned to the master database. If I
10:53open the databases here and go to my
10:55master database, the master database
10:57has a series of folders and also a
10:59security folder. If I open the security
11:02folder, here is my little users folder,
11:05right? And if I update this folder,
11:07here is my user Alberto, which could
11:09have been another name; the user does
11:11not necessarily have to be the same as
11:14the login. I could have used, as I say,
11:16the login for Judit, Carlos, or another
11:18. So, users are assigned to databases,
11:22and by doing this, what am I doing? I
11:25am granting access or permissions so
11:28that this user has access only to the,
11:31uh, master database. Even if I, if I
11:35generate a new SQL Server instance, I
11:37go here and I am going to log in with,
11:39with, with the Alberto login. Let's see
11:42what is going to happen. I'll go here
11:44and enter Alberto 1 2 3 4. It logs in
11:49without any problem. But if I want to
11:51open a database that is not master, for
11:53example, this one that says "my point
11:55of sale," I will get an error,
11:57obviously telling me that I don't have
11:59permissions, I cannot access those
12:01databases. Why? Because when I created
12:04him, I created him as a user who only
12:07has permission for the master database,
12:09right? If I wanted this user to have
12:13access to a database, this one for
12:16example, "my point of sale," I could,
12:19for example, disconnect this here and
12:21create another user. I am going to
12:25create a user here, for example. Oh no,
12:30the same user. I'm going to use another
12:32name. User, for example, Mario. Use
12:35Alberto's credentials, but have access
12:37to the "my point of sale" database to
12:40differentiate, right? The user can be a
12:43different user that uses the same login
12:46. In this case, I will create another
12:49user, Mario, who uses Alberto's login,
12:51but this time this user Mario has
12:53access to the "my point of sale"
12:55database. I select it, execute it, and
12:59it's already created. So, if I go to my
13:01database, "my point of sale," right?
13:04Inside security, I have the user Mario
13:07created. So, if I access again, I log
13:12in with user Alberto, sorry, login
13:17Alberto, no, remember, I do not log in
13:20with user Mario. User Mario is not used
13:23to access, right? Once again,
13:25credentials, the logins, are used to
13:27access. So, I access with the user,
13:30with the login Alberto. Now, with the
13:33login Alberto, I have a user we called
13:37Alberto, who has permissions for the
13:40master database, and I have another
13:43user called Mario, who has permission
13:48to access the "my point of sale"
13:49database. So, since I already accessed
13:52with the login Alberto, both users were
13:55detected quickly. If I open the "my
13:58point of sale" database, it will let me
14:00in because it already detected that I
14:02have a user related to that login,
14:04which allows me to access this database
14:06. But if I, for example, want to access
14:10the database, my company, I get an
14:12error because I don't have any user
14:14related to that login that has
14:16permission to access that database,
14:19right? So, that's where the difference
14:21lies. Users in SQL Server are used as
14:25permissions to access a specific
14:29database, right? They are, they are,
14:32they are a kind of role, right? Instead
14:35of assigning roles for a database,
14:37users with database permissions were
14:40created. But logins are simply the
14:42credentials; they are the usernames and
14:44passwords used to access the entire SQL
14:46Server environment. Now, it is true
14:50that with the same login we can have
14:53access to all databases without needing
14:57to create users, but that will only
15:00happen if we assign our login the
15:03administrator role. That is the only
15:06way; if our login does not have the
15:09administrator role, our login does not
15:11have access to any database, right?
15:14Let's do, let's do this experiment,
15:17right? So, we have created, for example
15:19, the user, the login Judit, and we
15:22have assigned her the administrator
15:24role. So, if I go here to log in with
15:29the login Judit, right? Password 1 2 3
15:364, there it is, it was generated down
15:39here. And since she has the
15:41administrator role, I can access all
15:43databases without any problem. But
15:46let's do one little thing. I'm going to
15:48disconnect her, I'm going to remove the
15:50administrator role. I could do it via
15:53code, but I'm going to do it over here.
15:58Let's see, let's go here. Here is the
16:00security folder. Double click, I go to
16:03my roles and I tell her, "No, you are
16:04not going to be an administrator." I
16:07remove it. Now, since she is not an
16:10administrator and I haven't created any
16:14user assigned to a specific database
16:18for this login, if I log back in with
16:24this credential, with this login, I
16:26will no longer be able to access any
16:29database because she is no longer an
16:32administrator and she doesn't have any
16:35user either. So, if I try to open my
16:37point of sale, or error, I could before
16:40, my company no, I can no longer. Why?
16:43Because she doesn't have the
16:45administrator role and has no user
16:47created. So there you are more or less
16:50seeing the differences between a user
16:53and a login. Now, to delete a users and
17:00a login; if we want to delete a login,
17:04and this login has users assigned to it
17:07, we first have to delete the users.
17:11For example, if I want to delete the
17:13Alberto login, we don't have an Alberto
17:15login, but we have two users created
17:18for this login. We created the user
17:21Mario, who has access to the "mi punto
17:23de venta" database, and before that, we
17:25created the user with the same name,
17:27Alberto, who has access to the master
17:28database. "mi punto de venta" is here,
17:32it has the user Mario, and the master
17:35database has the user Alberto here.
17:37Look, Master has the user Alberto. The
17:42"mi punto de venta" database has the
17:43user Mario. And both use the Alberto
17:46login. I could not delete the Alberto
17:48login. If I right-click on the Alberto
17:51login and select delete, an error will
17:53pop up saying, "You cannot delete it
17:55because there are users using this uh
17:57login." So I say, "Okay, then what I
18:01have to do now is delete the users
18:04first." I go to my master database,
18:06Alberto. I delete the users. I mean, no
18:09, before deleting the login, I delete
18:11the users. Done, the user was deleted.
18:15I go to my database, "mi punto de venta
18:17". I delete the user Mario. Done,
18:24that's it. And now, yes. it should let
18:29me delete the uh, Alberto user. If I
18:33right-click and delete. I mean, it
18:36should, because before it gave me that
18:38error that it gets stuck and well, it
18:40tells me there are still users using
18:41this login. Actually, we already
18:44deleted them, and it's because it gets
18:46stuck, right? I don't know if it's my
18:50SQL server or my installed instance; I
18:52can't identify it, but to fix it
18:54quickly, what I do is disconnect and
18:57reconnect so that it refreshes, you
18:59know? that the users have been deleted.
19:05There. Done, it was deleted. Well, now
19:24it really was deleted. As I said, it
19:26kind of gets stuck, right? I don't know
19:29if it's my database or if that's just
19:32how it's happening, but it seems to
19:35take time to update. So, it already
19:37detected that there are no users
19:39created and now it allowed me to delete
19:40the login. Right, so we'll leave it
19:43there. In the following lessons, we
19:47will learn a little more about users
19:49and logins.

About this transcript

This page contains the full transcript of Curso SqlServer #2 Credenciales (Login) y Usuarios (Permiso a base de datos) by Multiverso TI, generated from the public captions YouTube serves with the video. The transcript has 2,755 words across 391 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.