Question
Due to IT rules users have restricted permissions and cannot see the Archive DB under the myQA Platform. How can I grant a user with such restricted rights access to the Archive DB without getting trouble with the IT department?
Answer
Step 1
Open the SQL Server Management Studio and connect to the myQA instance.
Step 2
Unfold the security tree.
Step 3
Right-click on affected user - Properties.
Step 4
Select the Secureables tab and click Search.
Step 5
Select the option "The server XXXXXXX" (in this case: SC-PC0B4G6C-2\SQLEXPRESS)
Step 6
Select Grant for the permission View any definition.
Step 7
Switch to User Mapping.
Step 8
Check Map for your main and archive database. Furthermore, check db_datareader and db_datawriter for the main and archive database.
Step 9
Confirm the changes by clicking OK.
Step 10
Expand the Database tree and following the myQA Main/Archive Database tree.
Step 11
Expand the Security tree and following the Users tree. Right-click on the affected user - Properties.
Step 12
Switch to Securables and click Search.
Step 13
Select All objects of the types... and click OK.
Step 14
Select Stored procedures.
Step 15
Select the following Stored Procedures and grant Execute permissions for each on them
- SP_CREATE_SYNONYMS
- SP_CREATE_VIEWS
- SP_DELETE_SYNONYMS
- SP_DELETE_VIEWS
- SP_GET_DATABASE_INFO
- SP_REPAIR_INDEXES
Step 16
Repeat step 10 to 15 for the Archive database. Confirm the changes with OK. Now the user can see and use the Archive Database.