SQL Server 2016: Create a Database User

In SQL Server, you can create a database user to allow different users or processes access to a particular database.

Even though we have just created a login that can successfully connect to SQL Server, that login still can't access our Music database.

This is because we haven't yet associated that login with a database user.

There are four things you need to do in order to provide access to a database:

Create a login (done)
Create a database user (on the database that the user needs to access).
Associate that user with the login.
Specify the level of access the user is authorized for.

Launch the Database User Dialog Box

In the Object Explorer, navigate to Databases > Music > Security.

Right-click on the Users node and select New User....

You can also add a default schema. If left blank, it will probably default to the dbo schema.
Create the User

Enter the user name and login name.

In our case, make both the user name and login name Homer (this is the login name that we created previously).

Click OK to create the user and exit the dialog box.
Test the User

Now, connect using the login and try to access the Music database.

We now get further than we did in the previous exercise where we hadn't yet associated a database user for this login. We can now expand the node (which is more than we could do previously), and see its subnodes.

But when we expand the Tables node, we can't see any user tables. We can't see the tables that we created throughout this tutorial.

That's because we haven't specified the level of access for this database user.

We'll do that next.

You don't need to reconnect if you still have the connection open for this login. Simply navigate to the Music database.

Apply a Role

We'll now add the user to a database-level role. The role we choose will determine the level of access the user has within the database.

Launch the Database User Properties Box

In the Object Explorer, navigate to Databases > Music > Security > Users.

Right-click on the Homer user and select Properties from the contextual menu.
Select a Role

In the left menu, select Membership.

Check the db_datareader role and click OK.
Refresh the Object Explorer

Using the Homer connection, right-click on Databases > Music > Tables and select Refresh.

Also refresh the Views node in the same way.
The Result

We can now see that all the user tables and views have now appeared in the Object Explorer.

We can also retrieve data from the tables and views using a SELECT statement.