Overview

Logins can connect to the SQL server and have limited access to the master database. Users are specific to each database and must be separately created in each database to grant them access.

The login is a server-level principle and the user is a database-level principal.

Add Logins & Users

Windows

1. Connect to SQL server through RDP SSMS.
   In the example below, the server name is localhost (since the machine is the host) and authentication method is Windows, so no further input was needed.

   Open

   

2. Create a new login.

   1. In the Object Explorer, expand Security and right-click on Logins.

   2. Click on New Login.

      Open

      

   3. If using Windows authentication, click Search. Then type and add authorized Login name.
      If using SQL Server authentication, set Login name and enter password.

      Open

      
      Open

      

      Note: User must change password at next login is selected by default.

   4. Then click Ok.

3. Create a new database user.

   1. In the Object Explorer, expand the Databases folder.

   2. Expand the database in which to create the new database user.

   3. Right-click the Security folder, right-click the Users folder then choose New User.

      Open

      

   4. Input the desired Username.

      Open

      

   5. Click on the ellipses (…) to select the Login Name.

   6. In the Select Login window, type the login to be associated to. Confirm by clicking Check Names or Browse, then click Ok.

      Open

      

   7. Then click Ok again to finish the new database user creation.

Linux (Ubuntu 16.04 or 18.04)

1. Connect by running the following command, where servername is the SQL server name (or localhost for local connection) and sa is the username. Note sa is the default admin username.

   sqlcmd -S servername -U sa

2. Create a new login.

   CREATE LOGIN MyLogin WITH PASSWORD = 'Password12'; GO

   1. where MyLogin is the login name and Password12 is the login password.

3. Create a new database user.

   USE database_name; GO CREATE USER newuser FOR LOGIN MyLogin; GO

   1. where newuser is the new database_name user for the login MyLogin.

4. [Optional] You can authorize other logins to create more logins and inside a database you can authorize other users to create more users.

   GRANT ALTER ANY LOGIN TO MyLogin; GO USE database_name; GO GRANT ALTER ANY USER TO newuser; GO

   1. The login MyLogin can create more logins and the user newuser can create more users within the database database_name.

Add Admin Logins & Users

Windows

1. Connect to SQL server through RDP SSMS.
   In the example below, the server name is localhost (since the machine is the host) and authentication method is Windows, so no further input was needed.

   Open

   

2. Create a New Login.

   1. In the Object Explorer, expand Security and right-click on Logins.

   2. Click on New Login.

      Open

      

   3. In the General page:

      1. set Login name

      2. select SQL Server authentication

      3. enter password
         Note: User must change password at next login is selected by default.

         Open

         

   4. Select the Server Roles page in the left-menu, and then check the sysadmin check box in the Server roles list.

      Open

      

   5. Select the User Mapping page. Here, select which databases users associated to this login will have permissions to.

   6. In the Map column, check the box for the database that the new login can access. 
      By default, the login name appears in the User column.

   7. In the Database role membership for list, check the db_owner box.

      Open

      

   8. Click OK.

Linux (Ubuntu 16.04 or 18.04)

1. Connect locally by running the following command, where servername is the SQL server name (or localhost for local connection) and sa is the username. Note sa is the default admin username.

   sqlcmd -S servername -U sa

2. Create a New Login and assign admin role to it.

   CREATE LOGIN MyLogin WITH PASSWORD = 'Password12'; GO ALTER SERVER ROLE [sysadmin] ADD MEMBER MyLogin; GO

   1. where MyLogin is the login name and Password12 is the login password.

3. Create a new database user.

   USE database_name; GO CREATE USER newuser FOR LOGIN MyLogin; GO ALTER USER newuser WITH DEFAULT_SCHEMA=[dbo]; ALTER ROLE [db_owner] ADD MEMBER newuser; GO

   1. where newuser is the new database_name user for the login MyLogin.