Your Role Is Changing

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


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

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

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply