Creating SQL server login

SQL server login (object) is the means that which helps, user (client) to get access to the server there are basically two modes of authentication in SQL server

i)                    Windows authentication:
Windows authentication is the default and integrated security. This is used to grant access for the domain (Active Directory) users.

ii)                   SQL server authentication:

SQL authentication is the in server authentication that which requires you to provide password while creating an account.

 General tab

We need provide the login name in the space provided, we also need to choose if it windows authentication or SQL server authentication.

 Server Roles tab

In here, we are provided with the options of providing respective role for the login you are creating.

Below is quick one line description about these server roles 

Roles	Description
bulkadmin	Can run bulk insert statement on the instance
dbcreator	Can create, drop, restore databases
diskadmin	Can manage disk files
processadmin	Can handle any process running on the instance
public	Default role, inherits all the permissions as assigned to Public on the instance
securityadmin	Can change SQL login password, GRANT,DENY and REVOKE  server level/database – level permissions
serveradmin	Server admin role can shut down the server, change server-wide configuration  options limitations: can’t access user database
setupadmin	Role can create and drop linked servers. quite no other use of this role
sysadmin	Sysadmin is the highest role, can perform anything  in the server

User Mapping: Is the tab that which give you an option to create user on a particular database (system database or user database) providing respective database roles .

Example: In below figure, we are creating SQL Server Login for “SK”
Screenshot describers, his newly created roles on Adventure works Database 

Below is quick one line description about Database level roles.

Role	Description

db_owner	can  perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin	can add and remove users to the database roles
db_accessadmin	Can Grant, Deny and revoke user permission on the database
db_backupoperator	Can perform database backups
db_ddladmin	Can run any DDL command on the database
db_datawriter	can add, delete, or update data in all user tables.
db_datareader	can read all data from all user tables.
db_denydatawriter	cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

Click okay to create the login and user on the database ….

On the left top corner in options panel, you have script button to print script to query window or save it for further use

Below is the sample Script:

USE [master]

GO

CREATE LOGIN [sk] WITH PASSWORD=N'1#*#', DEFAULT_DATABASE= [master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [AdventureWorks]

GO

CREATE USER [sk] FOR LOGIN [sk]

GO

USE [AdventureWorks]

GO

ALTER ROLE [db_datareader] ADD MEMBER [sk]

GO