Your Role Is Changing

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  April 28, 2015 at 9:52 am

  #1793991

  Totally agree with Jeff and Eric. It isn't that everyone that will behave that way but *someone* will!!!

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• F. van Ruyven

  SSCommitted

  Points: 1507

  More actions

  April 29, 2015 at 7:09 am

  #1794273

  stakes (4/23/2015)

  ---

  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.

  use SpecificDatabase;

  -- 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.

  use master

  -- 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.