Creating a read-only SQL Server user Account

On this publish, We’ll discover ways to create a read-only SQL Server User Account.

You already know that ‘sa’ account has particular privileges required to manage the SQL Server database. The ‘sa’ account is a well-known SQL Server account and is prone to be focused by attackers and thus extra vulnerable to offering unauthorized entry to the database. This ‘sa’ default account is administrative.

Someday it’s possible you’ll must configure further SQL Server user accounts for different purposes to limit knowledge entry or read-only temper. The next directions are based mostly on Microsoft SQL Server 2014, however you’ll discover the procedures to be very related for different variations and/or editions. Let’s create a read-only SQL Server user account: 

1. Log in to the computer or server where SQL Server is put in with a Home windows user account with administrator privileges.
2. Open Microsoft SQL Server Administration Studio from Home windows Begin menu, within the Microsoft SQL Server folder.
3. Choose (native) because the Server Identify on the Connect with Server window, be sure Home windows Authentication is chosen and click on Join
4. Broaden the Safety folder within the panel on the left and Proper-click on the Logins folder and choose New Login…
   1. Enter the specified Login title(i.e. dev_user)
   2. Choose the SQL Server authentication possibility
   3. Enter and Affirm the specified password.
   4. Un-check the User should change password at subsequent login possibility
      • NOTE: You might also need to un-check the Implement password coverage possibility
5. Choose the Default database that corresponds to your want database
6. Click on User Mapping within the panel on the left
7. Test the Map field adjoining to the database for every firm to grant entry
   • NOTE: You possibly can examine the db_datareader possibility to supply full read-only entry to the whole database. In any other case, use the procedures beneath to grant entry to particular knowledge views.
8. Click on OK to save lots of and shut.

Use the next process to supply entry to particular knowledge views:

1. Observe steps 1-3 above to open SQL Server Administration Studio
2. Broaden the Databases folder within the panel on the left
3. Broaden the database that corresponds to the corporate (e.g., dbOrion)
4. Broaden the Views folder
5. Grant permission to every knowledge view for the SQL Server user account:
   1. Proper-click on the view and click on Properties
   2. Click on Permissions within the record on the left
   3. Click on Search…on the suitable and enter the brand new SQL Server login you created in step 5 above
   4. Click on Test Names then OK
   5. Test the Grant field for the Choose permission and click on OK
6. Repeat these step for every knowledge view.

Creating a read-only SQL Server user Account