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
Open SQL Server Management Studio and connect to the server instance.
In the Object Explorer pane, expand the Security folder.
Expand the Logins folder.
Select the specific user you want to grant access to.
In the Properties window, select the User Mapping page.
In the Map User to Database list, locate the database you want the user to access.
Check the box in the Map column next to that database.
In the bottom section of the window, select the appropriate database role for that user (e.g.,
db_datareaderfor read-only access ordb_datawriterfor read and write access).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.
