question

zillabaug avatar image
zillabaug 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?
securitydbapermissionssql server 2008 r2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
1 Like 1 ·

1 Answer

·
Tim avatar image
Tim answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.