SQL Server Login is used
to Access SQL Server means for Authentication
but Database users
are specific to a database
to perform database operation based on database role membership and the
permission under the tab securables means for Authorization.
After
creation of login named SQLLogin as
per above figure it will create the user in database Capital with user name SQLLogin
with default schema [dbo]
SQL script
for SQL Login as per above figure.
CREATE LOGIN [SQLLogin] WITH PASSWORD='random encrypted password', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
We can see in above image SQLLogin is marked as user in database
Capital thus it will
Create database user in database Capital using below SQL Script.
CREATE USER [SQLLogin] FOR LOGIN [SQLLogin] WITH DEFAULT_SCHEMA=[dbo]
Once SQL
server login and respective database user is created we cannot delete the SQL
Server logins if said SQL Login is used to access the SQL Server or database
user is still associated with said SQL Login.
Database Role members.
We can create Database user with
different types, shown in below figure
As per above
image from securables tab we can give required permissions/authorization to
database users for database objects (tables/view/sp’s etc.)
SQL Script to create user without SQL Login:
CREATE USER
[sqlwologin] WITHOUT LOGIN
WITH DEFAULT_SCHEMA=[dbo]
