How to Create Login

Here in this article we are going to show how to create login and user in Microsoft SQL Server.

• Using SQL server management studio- In object explorer click on server security tab. then right click on login to create a new login.

• A new window will appears on the screen as shown in the screen capture below

• Give a name for login and choose authentication mode. Using windows authentication is secure than SQL server authentication.

• Click on server roles to grant server-wide security privileges to login

 

Server-level role name	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server.
Serveradmin	Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin	Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
processadmin	Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin	Members of the setupadmin fixed server role can add and remove linked servers.
bulkadmin	Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin	The diskadmin fixed server role is used for managing disk files.
dbcreator	Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public	Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

 

• Click on User Mapping to map login to specific database and we can assign database roles to user for that database.

• Click on securables to give permissions to SQL server resources like Servers, Endpoints and logins. Securables are the resources to which the SQL Server Database Engine authorization system regulates access.

• Click on status to change the permission to grant or deny access to database engine, and we can enable or disable the login. And click OK.

Thus, we can create a new login in Sql server 2008 R2. Creating User

• Using Sql server management studio- In object explorer, click on a database (to which we are creating user) security.

• Right click on User to create a new user, and then a new window will appear on the screen as shown in the screenshot below. Give a user name and select the Server level Login, and also we can select database schemas (owned by the user), database roles.

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

• Click on securables to give permissions to objects like Databases, Stored Procedures, Tables, etc. Securables are the resources to which the SQL Server Database Engine       authorization system regulates access.

• Then click on Extended Properties. In using extended properties, you can add text, such as descriptive or instructional content, add input masks, and add formatting rules as properties of objects in a database or of the database itself. For example, you can add an extended property to a schema, a schema’s view, or to a column in the view. Because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way. This helps enforce consistency in the way data is treated by all the programs in the system.

By clicking OK, New user will be created for the Database DB2 in Test Login. Thus,  a new User will be created for that database to a Login in SQL server 2008 R2.

Database-level role name	Description
db_owner	Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin	Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin	Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter	Members of the db_denydatawriter fixed database role 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.