Minimum rights to read MS SQL

You are here:---Minimum rights to read MS SQL

To enable Microsoft SQL, vScope need access to the following:

  • Various SERVERPROPERTY-settings, to read general properties, like Edition and MachineName etc
  • vScope also needs read access to tables in the following databases:
    • master
    • msdb
    • model
    • sys
  • Various stored procedures are also run:
    • sp_who
    • sp_who2
    • sp_readerrorlog
    • xp_enumerrorlogs
    • sp_spaceused

vScope will work even if access is denied to some of the above objects, although the information will not be complete. vScope will never write anything to any database or read content from any user databases.

vScope use default instance port TCP 1433 and SQL Server Browser service UDP 1434. Other instances use random assigned ports. Make sure than no Firewall is blocking vScope from accessing the databases on those ports.

Guide to assign minimum permissions to a new user using SSMS

  1. Connect to your database instance with Microsoft SQL Server Management Studio (SSMS)
  2. Add a new user vscopesqluser by right clicking Security->Logins and selecting ‘New Login…’
    1. Enter Login name: vscopesqluser
    2. Select SQL Server Authentication
    3. Enter password: <your password>
    4. Select Server Roles page. Make sure the ‘public’ role is selected.
    5. Select User Mapping page. Make sure the following is true:
      1. Tick the ‘Map’ checkbox for all databases
      2. For all databases: The ‘public’ role should be ticked
      3. For system databases master, model, msdb and tempdb: Also tick ‘db_datareader’
      4. For system database msdb: Also tick ‘SQLAgentReaderRole’
    6. Click OK to create the user
  3. Right click the root in the Object Explorer (the instance). Select ‘Properties’
    1. Select the Permissions page.
    2. Click ‘vscopesqluser’ in the Logins or roles list.
    3. Make sure the following four permissions have the Grant checkbox ticked:
      1. Alter trace
      2. Connect SQL
      3. View any definition
      4. View server state
    4. Click OK to save permissions
  4. Right click Databases -> System Databases -> master -> Security -> Users -> vscopesqluser and select Properties
  5. Select the Securables page.
  6. Click ‘Search…’
  7. Select ‘Specific Objects…’ and click OK
  8. Click ‘Objects Types…’ and tick ‘Extended stored procedures’ and click OK
  9. In the ‘Enter the object names to select’, enter ‘error’ and click ‘Check Names’
  10. In the search results that appear, tick ‘xp_readerrorlog’ and ‘xp_enumerrorlogs’ and click OK
  11. Click OK again. The selected securables should now be listed under ‘Securables’
  12. For both the securables, make sure Grant is ticked for the Execute permission.
  13. Click OK
  • Right click Databases -> System Databases -> msdb -> Security -> Users -> vscopesqluser and select Properties
    1. Select the Securables page.
    2. Click ‘Search…’
    3. Select ‘Specific Objects…’ and click OK
    4. Click ‘Objects Types…’ and tick ‘Stored procedures’ and click OK
    5. In the ‘Enter the object names to select’, enter ‘help_job’ and click ‘Check Names’
    6. In the search results that appear, tick ‘sp_help_job’ and click OK
    7. Click OK again. The selected securable should now be listed under ‘Securables’
    8. For sp_help_job securable, make sure Grant is ticked for the Execute permission.
    9. Click OK
  • Done! You should now be able to scan your SQL database with vScope. Remember, every time you add a new database to your instance, make sure that you map your vscopesqluser to that database with minimum ‘public’ role. If you fail to do this, vScope will not be able to read information about that database.
2018-01-02T21:55:17+00:00