#
Create SQL Server User
This tutorial explains to you how to create a SQL Server database user. This article has a step-by-step approach.
What is the difference between a SQL Server Login and SQL Server User ?
SQL Login is for Authentication and SQL Server User is for Authorization. Authentication (using logins) can decide if we have permissions to access the server or not and Authorization (using users) decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.
You can create a Microsoft SQL Server Login using the SQL Server Management Studio.
The steps for creating a User for SQL Server Database
First, be connected to the Microsoft SQL Server Management Studio.
Right click on "Users" choose "New User ...". You will see the following page:
Choose User type, Login name (not always present), User name, default schema.
Here are the User types you can create under SQL Server:
Click on "Owned Schemas" and you will see the following page/ tab:
If a user own a schema, it owns some roles as well as the roles are within these schemas.
Choose if this user owns a schema or not and click on "Membership" tab.
Add other schemas to the SQL Server/ database user if you want and click on "Securables" tab.
Add permissions for this user. In my case I will let this user to "Alter any dataspace" on "mydatabase" database.
Click on "Extended properties" tab.
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.
Add some "extended properties" if you want and click on "OK".
... and here are the new SQL Server/ database user created.
Here is the code T-SQL for creating that SQL database user:
USE [mydatabase]
GO
CREATE USER [myuser] FOR LOGIN [mylogin] WITH DEFAULT_SCHEMA=[dbo]
GO