There are two methods to add a user in SQL Server. One way is to do so using code (programmatically), the second way is to use the interface. First we will explore the programmatic way, then we will walk through the interface.
First of all, there are two different ways users can login to SQL Server. One is automatically using their windows accounts or (Windows Authentication), the other is by using SQL Server Authentication. When a user is created in SQL using SQL Authentication, the user will have to type in the username and password manually in order to connect.
- Windows Authentication – The user will connect to SQL Server automatically using their existing credentials without having to type in their username or password. (More Secure)
- SQL Authentication – The user will be prompted to type in the username and password manually in order to connect. (Less Secure)
Add User Using Windows Authentication
-- Create user windows Authentication
CREATE LOGIN [YourDomainNameASPNETMVC] FROM WINDOWS
WITH DEFAULT_DATABASE = [YourDatabaseHere];
GO
-- Now add user to database
USE YourDatabaseHere;
CREATE USER ASPNETMVC FOR LOGIN [YourDomainNameASPNETMVC];
-- If adding to a second database, do so below:
USE YourSecondDatabaseHere;
CREATE USER ASPNETMVC FOR LOGIN [YourDomainNameASPNETMVC];
Add User Using SQL Authentication
-- Create user for SQL Authentication
CREATE LOGIN ASPNETMVC WITH PASSWORD = 'JinGleHeimerSchmidt'
,DEFAULT_DATABASE = [YourDatabaseHere]
GO
-- Now add user to database
USE YourDatabaseHere;
CREATE USER ASPNETMVC FOR LOGIN ASPNETMVC;
GO
-- If adding to a second database, do so below:
USE YourSecondDatabaseHere;
CREATE USER ASPNETMVC FOR LOGIN ASPNETMVC;
In order to use SQL Authentication, your SQL Server Instance must be set to Mixed Mode Authentication
The first step of creating a login only gets the user into the front gate of the community of databases. In order to get them into the databases themselves, you must create a user (tied to that login) for each of the databases they will access.
The next step is to add the user to a role.
Or you can view the entire Add User Script.