How To Create Login, User And Grant Permissions In SQL Server

Skip to content

How to Create New Login in SQL Server

Here is how to create login in SQL Server:

Step 1) To create login SQL server, Navigate to Security > Logins

Create New Login in SQL Server

Table of Content:
  • How to Create New Login in SQL Server
  • How to Create a User in SQL Server Database
  • How to Create User in SQL Server Management Studio
  • Create User using T-SQL
  • Assigning Permission to a User in SQL Server
  • Assigning Permissions in SQL Server Management Studio
  • Grant Permissions using T-SQL

Step 2) In the next screen, Enter

  1. Login Name
  2. Select SQL Server authentication
  3. Enter Password for MySQL create user with password
  4. Click Ok

Create New Login in SQL Server

Step 3) Login is created

Create New Login in SQL Server

You can also create a login using the T-SQL command for SQL server create login and user.

CREATE LOGIN MyLogin WITH PASSWORD = '123';

How to Create a User in SQL Server Database

A user is an account that you can use to access the SQL server. To create user SQL server, you can use any of the following two ways:

  • Using T-SQL
  • Using SQL Server Management Studio

RELATED ARTICLES

  • SSIS Tutorial for Beginners: What is, Architecture, Packages
  • SQL Server Database: Create, Alter, & Drop Database in SQL
  • SQL Server Tutorial PDF for Beginners (Free Download)
  • Top 20 SSRS Interview Questions and Answers (2024)

How to Create User in SQL Server Management Studio

Here is a step by step process on how to create a user in SQL Server Management Studio:

You will be creating a user for the EDU_TSQL database.

Step 1) Connect to SQL server to create new user

  1. Connect to SQL Server then expand the Databases folder from the Object Explorer.
  2. Identify the database for which you need to create the user and expand it.
  3. Expand its Security folder.
  4. Right-click the Users folder then choose “New User…”

Create User in SQL Server Management Studio

Step 2) Enter User details

You will get the following screen,

  1. Enter desired User name
  2. Enter the Login name (created earlier)
  3. Click OK

Create User in SQL Server Management Studio

Step 3) User will be created

User is created

Create User in SQL Server Management Studio

Create User using T-SQL

You can use the T-SQL’s create user command for SQL server add user to database. The SQL create user command takes the following syntax:

create user <user-name> for login <login-name> create user Guru99 for login MyLogin

Note: That the query should be executed within the query window. If a user is already created for a Login, SQL Server will throw an error if you create a user for the same login.

Assigning Permission to a User in SQL Server

Permissions refer to the rules that govern the levels of access that users have on the secured SQL Server resources. SQL Server allows you to grant, revoke and deny such permissions. There are two ways to give SQL server user permissions:

  • Using T-SQL
  • Using SQL Server Management Studio

Assigning Permissions in SQL Server Management Studio

Here is a step by step process on how to assign permissions to a user in SQL server management studio:

Step 1) Connect to your SQL Server instance and expand the folders from the Object Explorer as shown below. Right click on the name of the user, that is, Guru99 then choose Properties.

Assigning Permissions in SQL Server Management Studio

Step 2) In the next screen,

  1. Click the Securables option from the left.
  2. Click on Search

Assigning Permissions in SQL Server Management Studio

Step 3) In the next window,

  1. Select “All Objects belonging to the Schema.”
  2. Select Schema name as “dbo”
  3. Click OK

Assigning Permissions in SQL Server Management Studio

Step 4)

  1. Identify Table you want to Grant Permission
  2. In Explicit Permission select Grant
  3. Click Okay

Assigning Permissions in SQL Server Management Studio

Step 5) The user Guru99 is granted SELECT permission on table Course.

Grant Permissions using T-SQL

To grant permission to a user using T-SQL, you first select the database using the use statement. You then assign the permission to the user using the grant statement. Here is the syntax for SQL server create user and grant permission:

use <database-name> grant <permission-name> on <object-name> to <username\principle>

For example, the following command shows how you can grant the select permission to the user Guru99 on the object (table) named Course within the Database EDU_TSQL:

USE EDU_TSQL GO Grant select on Course to Guru99

The permission will be granted!

You Might Like:

  • sql-server-database-create-alter-drop-restore
    SQL Server Database: Create, Alter, &…
  • sql-server-table-create-alter-drop
    SQL Server CREATE, ALTER, DROP Table…
  • Top 50 SQL Server Interview Questions and Answers (%currentyear%)
    Top 50 SQL Server Interview Questions…
  • sql-server-introduction
    What is SQL Server? Introduction,…
Post navigation Report a Bug Previous PrevNextContinue Scroll to top Scroll to top
  • Home
  • TestingToggle child menuExpand
    • Agile Testing
    • JUnit
    • Quality Center(ALM)
    • Bugzilla
    • HP Loadrunner
    • RPA
    • Cucumber
    • Software Testing
    • SAP Testing
    • Database Testing
    • Mobile Testing
    • Selenium
    • ETL Testing
    • Mantis
    • SoapUI
    • JMeter
    • Postman
    • TEST Management
    • JIRA
    • QTP
    • TestLink
  • SAPToggle child menuExpand
    • ABAP
    • CRM
    • PI/PO
    • APO
    • Crystal Reports
    • PP
    • Beginners
    • FICO
    • SD
    • Basis
    • HANA
    • SAPUI5
    • BODS
    • HR
    • Security Tutorial
    • BI/BW
    • MM
    • Solution Manager
    • BPC
    • QM
    • Successfactors
    • CO
    • Payroll
    • SAP Courses
  • WebToggle child menuExpand
    • Apache
    • Java
    • PHP
    • SQL Server
    • AngularJS
    • JSP
    • PL/SQL
    • UML
    • ASP.NET
    • Kotlin
    • PostgreSQL
    • VB.NET
    • C
    • Linux
    • Python
    • VBScript
    • C#
    • MariaDB
    • ReactJS
    • Web Services
    • C++
    • MS Access
    • Ruby & Rails
    • WPF
    • CodeIgniter
    • MySQL
    • Scala
    • SQLite
    • DBMS
    • Node.js
    • SQL
    • Perl
    • JavaScript
  • Must LearnToggle child menuExpand
    • Accounting
    • Embedded Systems
    • Operating System
    • Algorithms
    • Ethical Hacking
    • PMP
    • Android
    • Excel Tutorial
    • Photoshop
    • Blockchain
    • Go Programming
    • Project Management
    • Business Analyst
    • IoT
    • Reviews
    • Build Website
    • ITIL
    • Salesforce
    • Cloud Computing
    • Jenkins
    • SEO
    • COBOL
    • MIS
    • Software Engineering
    • Compiler Design
    • Movie
    • VBA
    • Courses
    • Networking
    • VPN
    • Reverse Phone Lookup
    • Spy Apps
    • VoIP
  • Big DataToggle child menuExpand
    • AWS
    • Hive
    • Power BI
    • Big Data
    • Informatica
    • Qlikview
    • Cassandra
    • MicroStrategy
    • Tableau
    • Cognos
    • MongoDB
    • Talend
    • Data Warehousing
    • NiFi
    • ZooKeeper
    • DevOps
    • OBIEE
    • Pentaho
    • HBase
  • Live ProjectToggle child menuExpand
    • Live Agile Testing
    • Live Selenium Project
    • Live HP ALM
    • Live Selenium 2
    • Live Java Project
    • Live Security Testing
    • Live Mobile Testing
    • Live Testing Project
    • Live Payment Gateway
    • Live Testing 2
    • Live PHP Project
    • Live Telecom
    • Live Projects Hub
    • Live UFT/QTP Testing
    • Live Python Project
    • Live SEO Project
  • AIToggle child menuExpand
    • Artificial Intelligence
    • PyTorch
    • Data Science
    • R Programming
    • Keras
    • TensorFlow
    • NLTK
Toggle Menu Close Search for: Search

Từ khóa » đăng Nhập Sql Server