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?
Open the SQL Server Management Studio and connect to the myQA instance.
Unfold the security tree.
Right-click on affected user - Properties.
Select the Secureables tab and click Search.
Select the option "The server XXXXXXX" (in this case: SC-PC0B4G6C-2\SQLEXPRESS)
Select Grant for the permission View any definition.
Switch to User Mapping.
Check Map for your main and archive database. Furthermore, check db_datareader and db_datawriter for the main and archive database.
Confirm the changes by clicking OK.
Expand the Database tree and following the myQA Main/Archive Database tree.
Expand the Security tree and following the Users tree. Right-click on the affected user - Properties.
Switch to Securables and click Search.
Select All objects of the types... and click OK.
Select Stored procedures.
Select the following Stored Procedures and grant Execute permissions for each on them
Repeat step 10 to 15 for the Archive database. Confirm the changes with OK. Now the user can see and use the Archive Database.