How to create user and grant permission in SQL server?

Question

Ashutosh Patel · Answer

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 testLoginwith password,

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.

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 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 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-

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,

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,

Grant the SELECT Permission

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

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,

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.

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,

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

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?

forum

How to create user and grant permission in SQL server?

Can you answer this question?

1 Answers

SQL Server Create User and Grant Permission