---
title: "How to create user and grant permission in SQL server?"  
description: "How to create user and grant permission in SQL server?"  
author: "Sandra Emily"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160922/how-to-create-user-and-grant-permission-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# How to create user and grant permission in SQL server?

How to create user and [grant permission](https://www.mindstick.com/forum/161031/how-to-grant-permission-to-user-on-a-single-table-in-sql-server) in SQL server?

## Replies

### Reply by Ashutosh Patel

#### SQL Server Create User and Grant Permission

Once a user is created using the `CREATE USER` statement, the user has no permissions on database objects such as **tables**, **views**, and **indexes**.

## Create New User

For creating new users in the database, first, we created a login `testLogin`with password,

```plaintext
CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'
```

Note- use `master` database for creating any login in the SQL database.

Now, move to the current database to `MyCollegeDb` and create a user for `testLogin` login.

```plaintext
USE MyCollegeDb
GO
CREATE USER varans
FOR LOGIN testLogin;
```

When we create a new user in the database he doesn't have any permissions on the database objects like tables, views, and indexes.

To allow a user to interact with database objects, you must [grant](https://www.mindstick.com/blog/481/difference-between-grant-and-with-grant-in-sql-server-2008-r2) permissions to the user. For example, you can grant permissions so that a user can select data from a table.

## SQL Server GRANT statement

To grant [permission](https://www.mindstick.com/forum/159434/linux-service-permission-denied-error-adjust-permissions) to a user, you use the SQL `GRANT` statement.

The `GRANT` statement allows you to grant permission on the **securable** to the **principal**.

- A securable is a resource to which the SQL Server authorization system controls access. For example, a table is a securable.
- A principal is an entity that can request a SQL Server resource. For example, a user is a principal in SQL Server.

## Syntax-

```plaintext
GRANT permissions
ON securable TO principal;
```

## Explanation-

First, specify one or more permissions after the `GRANT` keyword. If you have multiple permissions, you must use a comma to separate the permissions.

Second, specify a securable after the `ON` keyword.

Third, specify a principal after the `TO` keyword.

## Example-

Let's create a table `People` in the database `MyCollegeDb` and inserts some values into it,

```plaintext
USE MyCollegeDb
GO
CREATE TABLE People (
 ID INT IDENTITY PRIMARY KEY,
 FirstName varchar(50) NOT NULL,
 LastName varchar(50) NOT NULL
);
INSERT INTO People (FirstName, LastName)
 VALUES ('John', 'Doe'),
 ('Jacob', 'Mark'),
 ('John', 'Smith'),
 ('Dach', 'Keon');
```

If you connect to SQL Server using login `testLogin`. You will see that user `varans` can access the `MyCollegeDb` database but can't see any tables. See in the below picture,

![How to create user and grant permission in SQL server?](https://www.mindstick.com/mindstickforums/b96fbea8-65c8-482c-8a38-4a19bac0a7a3/images/d37648b3-ed38-49b3-a779-632167609721.jpg)

## Grant the SELECT Permission

Switch to the system administrator connection and grant `SELECT` permission to the user `varans` on the `People` table.

```plaintext
USE MyCollegeDb
GO
GRANT SELECT
ON People TO varans;
```

Now, the user `varans` can see the `People` table and select data from it. For example,

```plaintext
SELECT * FROM People;
```

However, the user `varans` cannot insert or update data into the `People` table.

## Multiple Grant Permission

Grant the `INSERT` and `DELETE` permissions on the `People` table to the user `varans`.

```plaintext
USE MyCollegeDb
GO
GRANT INSERT, DELETE
ON People TO varans;
```

Now, switch to the user `varans` connection and insert a new row into the `People` table,

```plaintext
INSERT INTO People
VALUES('Maria', 'Jaen');
```

![How to create user and grant permission in SQL server?](https://www.mindstick.com/mindstickforums/b96fbea8-65c8-482c-8a38-4a19bac0a7a3/images/331180e3-f345-4612-80ea-c9613595a6a3.png)

Now, the user `varans` can insert data into and delete data from the `People` table.

**Also, Read:** [How to drop a user in SQL Server?](https://www.mindstick.com/forum/160924/how-to-drop-a-user-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160922/how-to-create-user-and-grant-permission-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.