Skip to main content

How to Grant a Microsoft SQL Server User Access to One Database

Grant a specific Microsoft SQL Server user access to a single database using SQL Server Management Studio (SSMS). This procedure allows you to control permissions for a specific user without granting them access to the entire server instance.

Grant a specific Microsoft SQL Server user access to a single database using SQL Server Management Studio (SSMS). This procedure allows you to control permissions for a specific user without granting them access to the entire server instance.

Before you begin

You will need:

  • A Microsoft SQL Server instance

  • Administrative access to the SQL Server instance

  • The specific username of the user you wish to grant access to

  • The name of the database you want the user to access

Granting user access to a specific database

  1. Open SQL Server Management Studio and connect to the server instance.

  2. In the Object Explorer pane, expand the Security folder.

  3. Expand the Logins folder.

  4. Select the specific user you want to grant access to.

  5. In the Properties window, select the User Mapping page.

  6. In the Map User to Database list, locate the database you want the user to access.

  7. Check the box in the Map column next to that database.

  8. In the bottom section of the window, select the appropriate database role for that user (e.g., db_datareader for read-only access or db_datawriter for read and write access).

  9. Click OK to save the changes.

Confirm the result

  • The user can now successfully connect to the specific database.

  • The user is restricted from accessing other databases on the server unless they are explicitly mapped to them.

Troubleshooting

User cannot connect to the database

If the user can connect to the server but receives a "permission denied" error when attempting to access the specific database, verify the following:

  • Ensure the correct database role (such as db_datareader) was selected in the User Mapping page.

  • Confirm that the database name in the mapping list matches the exact name of the database you intend for them to use.

Limitations

This procedure grants access at the database level. If the user requires specific permissions on individual tables or stored procedures within that database, those must be configured separately using the database's security settings.

Did this answer your question?