4. Security

Topics In This Module.

• Authentication Types in SQL Server 
• Types of Login 
• Windows Login 
• SQL Login 
• Creating Users and Logins 
• Server roles 
• Password policy 
• Understanding Database and Server Roles 
• Permissions 
• Working on access level issues 
• Orphan users Finding and Fixing 
• Important Queries 

 ***============================================================================***

Security of SQL Server Instance

Authentication

Logging into a server or a domain is called authentication, the authentication in SQL server is of two types they are:

1. Windows Authentication
2. SQL Authentication.

Windows Authentication One who has access to server and the user is in SQL logins as domain login or group login can access the server with this type, simply said Domain accounts or OS accounts are used in Windows authentication.

Note: All windows/OS level security policies get applied to this login/user hence this authentication is called Trusted Connection/Authentication

SQL Authentication A separate login name and password is passed to users who wants to authenticate using this,
There is a default login which is created after installation its "sa" which is by default SQL Authentication, we can change the "sa" login password any time.

• Mixed mode accepts two types of connections.
• The Mixed mode connection need to be enabled at server level for the user to access using SQL authentication. (on server tab->Connections->mixed mode or both)

in 2008 SQL server and in past version, thee used to be a login by default called as "built-in administrator" using which whoever have admin privileges at OS level will have access to SQL server as sysadmin. to prevent security threats the login will be deleted post installation, which will prevent admins at OS level to login to SQL server.

--------------------------------------------------------------------------------------------------------------------------------------------------------

The logins in SQL server can also be divided in to two they are:

1. Domain/Individual login and - Mostly used for Windows authentication but we can also use it for SQL Authentication.
2. Normal login - only used for SQL authentication.
3. Group Login: a login which has a user as a group at windows level. That group can be added here.

Note: The User must be present at AD level to create a login with windows authentication

To add users at AD level, try lusrmgr.msc --- give a practical try.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Login Creation

When creating the login just give the domain level login name from windows options.

Create login <login name as is in domain> from windows;

When creating the login give the login name and password if it’s a SQL authentication,

Create login <any login name> with password = ‘*******’;

And there are some password policies in SQL authentication they are:

• Enforce Password Policies: all OS password policies where SQL is installed are applied to this login.
• Enforce Password Encryption: checking this will encrypt the password using different methods like certificate or key ...etc.
• User Must change the password on next login: checking this will give a pop up to user when he logins to the server for the first time.

And we can also select the default databases, which means when user connects to the server the session is opened using which databases we can select that here.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Principles

A login or an application which is using or accessing the SQL server resources is called a principle.
Principle is at three levels they are:

1. OS level - Accounts or Groups is called Windows principle.
2. SQL Level - SQL server Logins is called SQL principle.
3. Database’s level - Databases users is called DB principle.

Securables

Securables are nothing but Objects will be accessible by Principles.

Securables are also at three levels they are:

1. SQL Server level
2. Databases Level
3. Schema Level.

Permissions: What operations a principle can be performed on a securable is decided by this.

These are of two types of permissions they are:

Roles: the roles in SQL server are at two levels they are

1. SQL Server Roles: At server level there are Fixed server roles & custom / user  created roles.
2. Databases Roles: At Databases level there are Fixed Database roles and Custom/user created roles.

DCL's: DCL are data Control languages they are three types they are: 

1. Grant: used to grant any permission to user or login.
2. Revoke: Will completely remove the permission, and also unblock the denied permissions.
3. Deny: Which will block the granted roles/permissions, high priority.

Options:

• With grant: when this option is used and granted a permission to a user then he can share that permission to others as well
• Cascade: this is a revoke option used to revoke all the permissions granted using with options, all the permissions share to others will also be revoked.

Syntaxes: 

• grant <permissions> on <object> to <user>;
• revoke <permissions> on <object> from <user>;
• grant <permissions> on <object> to <user> with grant;
• revoke <permissions> on <object> from <user> cascade;
• deny <permissions> on <object> to <user>;
• sp_addrolemember '<role>','<user>';
• sp_droprolemember '<role>','<user>';
• sp_addsrvrolemember '<login name>','<role name>';
• sp_dropsrvrolemember '<login name>','<role name>';

--------------------------------------------------------------------------------------------------------------------------------------------------------

User creation and login creation and dropping them

Syntaxes:

• create user <username> for login <login name> with default_schema <schema name>
• create login <login name> with password = ‘**********’ --- for SQL Authentication.
• create login <login name> from windows --- for Windows Authentication

--------------------------------------------------------------------------------------------------------------------------------------------------------

There are 9 Fixed server roles they are (BD^2 PS4):

1. Public: with this role a user can just connect to server this is by default granted to login whenever a login is created.
2. Sysadmin: this is the complete administration privileges highest privileges that a login can have.
3. Security admin: Can see/create/manage the logins and all the security related things.
4. Disk admin: If we want to create a backup devices/disk, to manage the disk space allocation s we use this privilege.
5. Server admin: To change all the server level configuration we use this which means all server level configurations and down to that configuration can be managed with this. 
6. DB creator: to create/drop/manage alter/rector the databases we use these privileges. 
7. Bulk Admin: To perform any bulk operation we need these privileges to be granted. 
8. Process Admin: To view/kill/manage all the processes we use this sp_who or sp-whos2 or sys. processes;
   using this we can start the SQL Server services as well
9. Setup admin: To configure the linked server with command line we use these privileges but to create the linked server with GUI mode we need to have sysadmin privileges

There are 10 Fixed Databases roles they are:

1. Public: with this role a user can just connect to the database this is by default granted to user whenever the login is mapped to a database.
2. db_datareader: To give the read permissions on a database to a user we use this role.
3. db_datawriter: To give the write permissions on a database to a user we use this role.
4. db_denydatareader: to deny the reader permissions on a database, if we give the owner and this role together the user can do everything except reading the databases.
5. db_denydatawriter: to deny the writing permissions on a database, if we give the owner and this role together the user can do everything except write the databases.
6. db_owner: This is the admin privileges on a database and this is the highest privileges that a user can have.
7. db_backupoperator: the user to take backup of a database.
8. db_secrityadmin: to create a user and manage the security at db level we use these privileges
9. db_ddladmin: To execute the ddl commands against to a database a user should have these privileges (create, alter, drop, rename, comment)
10. db_access admin: to give access like to grant, revoke, deny the privileges we use this
11. guest. --- temp

Some of Default – Additional roles only in MSDB database are:

• DatabaseMailUserRole
• db_ssisadmin
• db_ssisltduser
• db_ssisoperator
• SQLAgentOperatorRole 
• SQLAgentReaderRole
• TargetServersRole
• dbm_monitor
• dc_admin
• dc_operator
• dc_proxy

Note: A user can be associated with only one login, but a login can be associated with many database users (whenever a login is mapped to a db a separate user is created in that database)

Custom roles

Custom roles at server level and custom roles at databases level there are two types they are:

• Custom server roles -- at server level
• Custom databases roles -- at databases level
• Customer application role -- at databases level

These are user created roles which are created based on requirement.

custom database level roles can be directly mapped to users, but custom application roles cannot be directly mapped it's a database and session level roles when session is disconnected the role is revoked and to grant the role the user himself need to grant it, and also this role is protected by a password hence this cannot be directly mapped to users.

we can only create the role and create the user required the user himself need to run the below query to get the role

sp_setapprole <role name>, <password>;

This role is valid thorough the session only when disconnected that’s revoked.

==================================================================================

Schema

Schema is a logical container in the databases which provides name space for different objects.

User defined/ named schemas can be created from 2005 SQL server version, before this there is only one schema dbo.

Every db user is associated with a schema, and every schema need to have a user as an owner of that schema,

Advantages:

• Avoiding naming conflicts in a database, suppose there are two table with same name usually without schemas we cannot create them, but using schemas we can one table will be in a schema and another table will be in a different schema, but overall all the table are in a single database.
• And also, it’s easy to grant the permissions on an object, grouping all the objects on a schema and granting permissions on them would be easy. schema level permissions.

Note: 

A user cannot be deleted if the user is an owner of any schema, so its recommended to keep the owner for any schema as dbo user, as dbo user is the default user one cannot delete it.

dbo schema is a default schema in all databases and owner of the dbo schema is dbo user.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Orphan users: 

As users are considered as child and login as parents in SQL server, when a user is not associated with a login or a user is present at db level and the corresponding login is not present at Server level then that user is called an orphan user.

A login and a user are mapped/bounded using a SID,

To identify and fix the orphan users

sp_change_users_logins is the stored procedure used to find and fix the orphan users

Parameters passed to stored procedure are below:

• 'report' --- will list all the orphan users.
• 'update_one','username','login name' --- is used to fix the orphan user: to fix the orphan users the login at server level need to present, if its mandate to fix the users create a login at server level with similar name or diff and use it to fix. 
• 'autofix' ----