SQL Server User Permissions

Instrument Manager's access to SQL Server and a SQL Server database is controlled by the user's permissions in SQL Server. There are various permissions you may require depending on the level of access that a particular user will require in the Instrument Manager database and in the overall SQL Sserver.

SQL Server Administrator

Any user with the Server Role of "sysadmin" will have full access to the server and no other specific permissions are required. Instrument Manager does not require this permission. All Instrument Manager tasks can be performed by users with lesser permissions. However, if a user login with "sysadmin" permission is available then that user login will be suitable for any database creation and/or modification that may be required.

Note that an Instrument Manager Administrator is not the same concept as a "SQL Server administrator". The Instrument Manager administrator applies to an Instrument Manager login with the permission to create and modify Instrument Manager user logins. The Instrument Manager administrator term implies no special privileges for the SQL Server. The user may choose to apply the permission to create and/or modify the structure of an Instrument Manager database to only the Instrument Manager administrator user, but this is not necessary. It is possible for a regular Instrument Manager user to have the permission to create and/or modify the structure of a database.

Note that a Windows Authentication login to SQL Server can only be used when you are logged into Windows as that user. Thus, if you are using Windows Authentication, then it is likely that a particular user will have the permission to create or modify databases while other users will need to request the time and assistance of this particular user and the changes will most likely need to be done from this particular user's workstation. A SQL Server Authentication login can be used from any workstation by entering the username and password when requested. Thus, even though the SQL Server Authentication may still be known to only one user, they can use this login on any Windows user's workstation.

SQL Server permissions required by task

Permission to create new project databases

Required for Instrument Manager to create a new database.

Instrument Manager will give you the opportunity to enter a suitable administrative login if the current user does not have the permission to create a database.

• Server roles:
  • dbcreator (sysadmin can do this too)
• Database roles:
  • No specific roles are required because the user will be the owner.

Permission to modify the structure of the Instrument Manager database

Required to create or modify tables to match new or updated custom loop diagram templates.

Required to update the database structure to match an updated database template.

This user permission is appropriate if a SQL Server administrator in an IT department will create an empty database, then this user (not the IT administrator) will cause Instrument Manager to create its tables inside that empty database. In this way all Instrument Manager users have their permissions restricted to within the Instrument Manager database and do not require any server roles.

Instrument Manager will give you the opportunity to enter a suitable administrative login if the current user does not have the permission to modify the structure of the database.

• Server roles:
  • No server roles are required, only database roles.
• Database roles:
  • db_ddladmin (sysadmin can do this too)
  • db_datareader
  • db_datawriter

Permission to modify the contents of the Instrument Manager database

Required to add, delete or update component records.

• Server roles:
  • No server roles are required, only database roles.
• Database roles:
  • db_datareader
  • db_datawriter

Permission to synchronise data from a linked P&ID database on SQL Server

Required (by all users) to check for changes in the P&ID database that is linked.

Permission is required in the server hosting the P&ID database, which is not necessarily the same as the server hosting the Instrument Manager database. This permission will be required in SQL Server even if the Instrument Manager database is stored in MS Access.

This permission is required for all regular Instrument Manager users if the Instrument Manager database is linked to a P&ID database that is hosted on SQL Server, not just by an administrative user.

You can find this permission in your SQL Server management tool under Server > Properties > Permissions, select a user and you will find "View Server State" at the bottom of the permissions list.

• Server permissions:
  • View server state
• Database roles:
  • No database roles are required. This is only available as a server role.