Eric M Russell (4/23/2015)
If a database architect, IT support, executive, or BI developer needs eleveated permissions to troubleshoot performance issues or run ah-hoc queries in production, you can setup something like a power user role, but NEVER grant them SYSADMIN or DBO membership.
-- grant select on any table or view:
exec sp_addrolemember db_datareader, 'DBPowerUsers';
-- grant user permission to view execution plans:
grant showplan to 'DBPowerUsers';
-- server level permissions require that context be changed to [master] database.
-- grant user permission to view object schemas:
grant view any definition to 'DBPowerUsers';
-- grant user permission to view system tables and views:
grant view server state to 'DBPowerUsers';
-- grant user permission to start sql profiler traces:
grant alter trace to 'DBPowerUsers';
Good point and thanks for sharing the script! Granting them access to a readable secondary can also work.
I agree. If you allow ad-hoc queries on a primary, first make sure tempdb is on its own, very large disk.