Contents
1
Server Level Login
1.1
Type of Logins
1.1.1
Windows
-
User - Login is related to
server. User created in Login and can
map to multiple databases. Login id
available in Master DB. Userid is related
to particular Database. SQL userid available in that particular DB.
-
Server Authentication >>
SSMS > <Server> >Security > it will show server authentication
level
1.1.2
SQL Server
2
DB Level Login
2.1
Type of Users
1. Windows
User
2. SQL
User with Password
3. SQL
User with Login
4. SQL
User without Login
5. User
Mapped to a certificate
6. User
mapped to an asymmetric Key
2.2
Organie Below <Starts>
2. TSQL - 12
types of users
1. Create
User
2. Permissions
- Security Principals
1. SERVER
LEVEL
1. Logins -
Loging to the DB engine
2. Fixed
Server Roles - Server - Level Permissions
3. User-defined
server Roles - support from 2014 - Not supported in Azure
4. DB users
(Single login or contained in the db ie No users)
2. DB Level
1. Fixed DB
roles
2. User
Defined DB Role
3. Assigning
Permissions
1. Authorization
- Grant / Revoke / Deny
2. Permission
- What is allowed or Deinied
3. ON
SECURABLE::NAME
4. Principal
> Login / user /role or group
4. User
Access
1. Table
Permissions - SELECT / INSERT / UPDATE / DELETE
2. View
Permission - SELECT
3. Stored
Proc - EXEC
5. How to
grant Access?
1. Select the
user > Securables OR
2. Select the
Object (eg Table) > Properties > Permissions OR
3. Script
6. Others
