asked
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?
what does it mean to troubleshoot database access? Does that mean connect to the database? Query a table? Execute a procedure? something else? will they be running dbcc checkdb to look for corruption? That's usually something that a DBA sets up for all databases on a server, not something you would have an individual run on demand. Did you mean something else by corruption issues?
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.
18 People are following this question.
