SQL Server Security

To understand the security feature of SQL Server you need to understand the levels of security.

So what happens at windows level, you need to user and password. As soon as you are inside windows you need to enter inside instance and for that you need a login. After entering into an instance you need to have users for each database. There will be mapping of users and login. So the main points are:

•  To enter into an instance we need a login.
•  To enter into database we need a user.
•  Users and logins must be mapped.

When a machine is on a network, it can be:

•  Workgroup or 

•  Domain

Workgroup is a standalone machine which may or may not be connected to a network and all the machines will be independent of each other while domain is a centralised machine where Active Domain(AD) is installed therefore it is called a Domain Controller(DC). So it is not needed in this case to go on each machine separately. But while installing windows we need to install it separately on domain and workgroup for each machine.

Types of Login

There are basically 3 types of logins in SQL Server. They are:

• Domain Login - It is the centralized login. Windows professional will create the login and will give it to every other machine. 

• Local Login - This login is local to the windows level. It will be created in windows operating system and after that it will be added to the SQL Server. this login would be local to the machine on which it is created.

• SQL Login - It is a login created at the SQL level and windows doesn't knows about this login. In order to connect to the SQL Server authentication you need to enable SQL Server and windows authentication mode. In order to apply the change if you switch from windows to SQL mode you need to restart SQL Server service.

How to create SQL Server Login?

• Go to SQL Server management studio. 
• Go to Security->Logins->Right click on login->New Login->Give Login name->Enable SQL Server authentication mode->Give PWD & confirm Pwd->Uncheck enforce pwd policy, enforce pwd expiration, user must change pwd on next login->click on server roles on left pane-.check Sysadmin->ok.
• Right click on instance->Properties->Security->Enable SQL Server & windows authentication mode->ok.
• Go to SQL Server Configuration manager->Restart SQL Server Service.

How to create Local Login?

• Go to start-> Administrative Tools->Computer Management->Local users and groups->Right click on users->New User->Give Username, full name, description->Give pwd and confirm password->uncheck user must change pwd at next logon->Create.
• Go to groups->Double click->Administrators->Double click->Click on add->Enter the username you just created->Ok->Apply->Ok.
• Go to management server studio->Security->Logins->Right Click->New Login->Add the username like this--Servername\Username->Go to Server roles->Check Sysadmin->ok.
• Log off Windows.
• Give pwd for the username just created.
• Open management Studio->Windows Authentication->ok.

How to create a Group?

• Start->Administrative tools->Computer management->Users & Groups->New Group->Give group name->Add->Enter name of the username that you want to include in the group->Ok.
• Go to management studio->Security->Login->Right Click->New Login->Add the login just created like this--Servername\Groupname->Server Roles->Check Sysadmin->Ok.
• Log off the system so that the windows Knows that the group has been created.
• Login again & try to connect.
• Group has been created.

Concept of Login

The box or system has operating System Windows which can have either:

• Local User or
• Domain User

After logging in into the box we need to enter into SQL Server instance which can have 3 logins:

• Domain
• SQL or
• Local

After logging in into the instance we can see databases. Each database is having a user which must be mapped to the Login created. We are able to use the databases without creating users if while creating instance, sysadmin permission is given in Server roles.

Two stages are required for entering into the instance or databases:

• Authentication-> Are you the same person?
• Authorization->What permissions you have?

Permissions

There are 2 levels of Permissions:

• SQL Server Level(Instance Level)
• Database Level

Whatever you see in Server roles(Security->Login->New Login->Server Roles) are instance level roles. There are total 9 Server Roles.

Logins & usernames are together called as Principals and resources on which permissions need to be given are called Securables. Permissions are the link between Principals and Securables.

If we need to create lot of logins and give same permissions to them, it will be time consuming. So we group logins and assign them roles(Grouping of Permissions) to ease the task.

Creating User of a database

• Create new Login.
• Map this login to the database by right clicking on database and then clicking on properties. Click on database->Security->Users->New User->Username whatever you wat and login name that you created in login->Give role members and click on Ok.
• User gets created in each database.

Some Useful commands

1. To see all the logins and Usernames: 

    select * from sys.server_principals

2. To see all the database related information:

    select * from sys.database_principals

3. To see all the schema related info:

    select * from sys.schemas

4. To create your own schema:

    create schema schema_name

5. To see info about Logins:

    select loginname, * from sys.syslogins [For login names only]

OR

    select * from sys.syslogins

6. To see info about users:

    select * from sys.sysusers

7. To see the version of SQL installed:

    select @@version

8. To see edition of SQL installed:

    select SERVERPROPERTY('edition')

9. To see server name:

    select @@SERVERNAME