How to Create a New User in SQL Server?

Learn how to create a new user in SQL Server using SSMS.

Open SSMS and select New User from the menu option.

Provide your user name; in my case, its sample_user

Choose the authentication and choose the default database.

The default database indicates the database to which the user has access. On the next page, you can specify the level of access to the user. The next drop-down is Default language -> I have made it as default, and it's English.

Arabic, Brazilian, British English, Bulgarian, Croatian, Czech, and Danish are some of the languages listed.

In the next window, you can specify the users and server roles. I have informed that this person is a sysadmin and public too.

The next option is "User Mapping"; the database will be listed here. You can specify the level of access to be provided to this user for each database.

I do not want to provide any other access to this user except "VenkatDB."

The next screen is securable; you will have a list of objects like Server, Endpoints, and Logins.

You can choose the object you want to provide or deny access to.

For example, I have selected Server -> Select an option "Create any database" and -> Select deny option. Here, we have restricted the user from creating any database. Still, he is a sysadmin, but he can't create any database.

The final page is status. It will define the current state of the user.

Now, let's move to our actual topic.

How do we prevent access to a database for a particular SQL Login user?

Consider am having a user who has access to multiple databases. I want to restrict the user from using "VenkatDB" - > A specific database.

Now, you need first to make the user public.

Provide necessary access level in the database level. Make the user public for that specific database. Now click "OK." Reopen the login page for that particular user. "VenkatDB" is disappeared.

Now, I am trying to access the objects in the database. Below is the message received. "The server principal "venkat" is not able to access the database "VenkatDB" under the current security context."

Summary

This article taught you how to create a new SQL Server user using SSMS.