Problem: What is a database user, SQL Logins and what are the difference between
a user and a login in SQL Server?
Solution:
SQL Logins:
Logins
are the credentials that authenticate connections to an instance. Except in the
case
of
an instance configured to support contained databases, a database user must map
to
an
existing SQL Server login. You can differentiate SQL Server logins based on the
type of
authentication
method used. SQL Server 2012 supports the following login types:
- Windows-authenticated login
- SQL Server–authenticated login
- Certificate
- Asymmetric key
A security
principal must have the ALTER ANY LOGIN permission to be able to create SQL
logins.
you can look in the below picture to see a list of logins in a SQL Server instance under security folder.
Users:
Database
users represent the identity of a SQL Server login when connected to a
database. Each database user maps to a SQL Server login except in the special
case of contained databases. You can configure a database user to use the same
name as a login to simplify the process of quickly identifying the relationship
between a database user and a SQL Server login. You can map a SQL Server login
to only one database user in each database. You can map a single login to
different users in different databases if you follow the “one database user per
SQL Server login per database” rule. You can assign database-level permissions
directly to database users. It is best practice to assign database-level
permissions to database-level roles and then add database users to those roles.
you can see in the below picture where to look for users.
Differences between
users and logins:
Although the terms login and user are
often used interchangeably, they are very different.
- A login is used for user
authentication
- A database user account is used
for database access and permissions validation.
Logins are associated
to users by the security identifier (SID). A login is required for access
to the SQL Server instance. The process of verifying that a login is valid is
called "authentication". This login must be associated to a SQL
Server database user. You use the user account to control activities performed
in the database. If no user account exists in a database for a specific login, the
user that is using that login cannot access the database even though the user
may be able to connect to SQL Server. The single exception to this situation is
when the database contains the "guest" user account. A login that
does not have an associated user account is mapped to the guest user.
Conversely, if a database user exists but there is no login associated, the
user is not able to log into SQL Server instance.
When a database is
restored to a different server it contains a set of users and permissions but
there may not be any corresponding logins or the logins may
not be associated with the same users. This condition is known as having "orphaned
users."
0 comments:
Post a Comment