Skip to content

How to save WhatsApp status videos?

Courses You Need to Learn Other Than Coding

How to Restore Database in SQL Server

How to Create Login and User

How to Upgrade SQL Server 2005 to SQL Server 2008

Differences Between SQL and NoSQL

Geeks-In-Tech

All about Technology, News, Tricks and Tips, SQL, Web Development.

  • Home
  • SQL
  • How To
  • Others
    • Mobile
    • Laptop / PC
create login and user

How to Create Login and User

May 21, 2018May 23, 2018 Nikhil Raj

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.
Comments
 SQL create login, create user, login, sql, user

Post navigation

← How to Upgrade SQL Server 2005 to SQL Server 2008
How to Restore Database in SQL Server →

Like Us on Facebook

Advertisement




Contact Us

  • Email
    admin@geeksintech.com

GEEKSINTECH.COM

  • Home
  • About Us
  • Contact Us
  • Disclaimer
  • Privacy Policy

Recent Articles

  • How to save WhatsApp status videos?
  • Courses You Need to Learn Other Than Coding
  • How to Restore Database in SQL Server
  • How to Create Login and User
  • How to Upgrade SQL Server 2005 to SQL Server 2008
Proudly powered by WordPress | Designed by: Geeks.

Geeks-In-Tech

  • MySQL
    • PSQL
    • Back
  • Database
    • RDBMS
    • Back