What is the best permissions to set for only troubleshooting databases access issues?
I want to create a login for a new user who could only have access to one of many databases in an instance.The user should have the appropriate rights and permissions to troubleshoot database access and corruptions issues. What would be a good set of roles/permissions to use to implement this?
It depends on what all you need to accomplish. Since you mentioned troubleshooting corruption issues, DBCC requires db_owner. Other DBCC commands that you tend to use with troubleshooting is DBCC INPUTBUFFER which requires sys_admin. If you need to limit the user to a single database in the instance, then you would need to limit them to dbo for that database to prevent them from making system level changes. Also depending, if you are having to question this persons ability to not mess up anything else, but the politics prevent you from saying NO to them having access, you could try to limit them by migrating this database to a dedicate instance. Isolate it to minimize exposure.