The following steps detail how to create a read-only user for an SQL database using Microsoft SQL Server Management Studio.
This may be helpful if you need to give access to your database but do not want the user to make any changes (such as new tables or records).
Note: The user will be able to run any stored procedures which are a part of the database. Depending on your configuration, these stored procedures may be able to create or delete records. As with any database management, understanding your database configuration and contents is critical to ensure the security.
How to create a read-only SQL Server 2008 account
- Using Microsoft SQL Server Management Studio, log into your database using a full access account
- Expand ‘Security’ then right-click on ‘Logins’.
- Select ‘New Login’. The new login window will open in a new window.
- Under ‘Login name’ enter an appropriate name for the account, for example ‘db1_readonly’
- Select ‘SQL Server Authentication’
- Under ‘Password’ and ‘Confirm password’ enter and appropriate password
- Select ‘User Mapping’ from the left hand list
- Under ‘Users mapped to this login’ select the database the user will have access to
- Under ‘Database role’ elect db_datareader’
- Click ‘OK’ to save the changes.
