Add User to Database

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

[cc lang=”sql”]
— Create user windows Authentication
CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS
WITH DEFAULT_DATABASE = [YourDatabaseHere];
GO
— Now add user to database
USE YourDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs];
— If adding to a second database, do so below:
USE YourSecondDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs];
[/cc]

Add User Using SQL Authentication

[cc lang=”sql”]
— Create user for SQL Authentication
CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’
,DEFAULT_DATABASE = [YourDatabaseHere]
GO
— Now add user to database
USE YourDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN JohnJacobs;
GO
— If adding to a second database, do so below:
USE YourSecondDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN JohnJacobs;
[/cc]

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.

6 comments
fares 26 Jul 2018 at 8:54 am

I used this

md.shahadat hassain talukdar 05 Aug 2017 at 7:33 am

how to add a linked SQL server like bill master connection database

Solomon 13 Nov 2014 at 6:59 pm

I used this and it felt like a little piece of joy.

Sam 29 Aug 2015 at 8:48 am

Saranam my beloved Swamiji.Annamalaiku nigar annanalaiyame(Paramahamsa nithyananda) enbathai ulagame yetru kondathu.. vaalum avathara purusarana engal guru vaalum kalathil nangalum vaalvathai yenni nangalum muguntha perumitham adaigirom antha perumithathutan ungal padangalai vanagugirom. You are the demigod of this cosmos.. Who shining forever in this cosmos as The Avatar.. You are a sacred secret in everything. You are The Cosmic Mother.- ma nithya suparnananda, Tamilnadu.

Ramesh 13 May 2014 at 7:45 pm

Brilliant Ans

m44u 03 Oct 2013 at 12:25 pm

Featured Articles

 Site Author

  • Thanks for visiting!
css.php