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.