locked
use of dynamic management views RRS feed

  • Question

  • I have found that there is some very good information available inside of the dynamic management views related to Service Broker, however I have also found this to be problematic as by default only the dba's at my company had access to these views and it took me several days to get a login that could see them. Just a thought, but it would be nice if this data were available using a view that is more accessible.
    Monday, August 13, 2007 4:22 PM

Answers

  • One can always use code signig to grant fine grained control to the needed funtionality to less priviledged users.

    Here is an example. While is not for the faint of heart, it works. The results are rock solid safe from security point of view. Your dba can create procedures for every needed view.

    Code Snippet

    use master

    go

    create procedure usp_view_sys_dm_broker_activated_tasks

    with execute as owner

    as

    select * from sys.dm_broker_activated_tasks;

    go

    create certificate sign_usp_view_sys_dm_broker_activated_tasks

    with subject = 'usp_view_sys_dm_broker_activated_tasks',

    start_date = '10/14/2007';

    go

    add signature to dbo.usp_view_sys_dm_broker_activated_tasks

    by certificate sign_usp_view_sys_dm_broker_activated_tasks;

    go

    create login login_sign_usp_view_sys_dm_broker_activated_tasks

    from certificate sign_usp_view_sys_dm_broker_activated_tasks;

    go

    grant authenticate server to login_sign_usp_view_sys_dm_broker_activated_tasks;

    grant view server state to login_sign_usp_view_sys_dm_broker_activated_tasks;

    go

    alter certificate sign_usp_view_sys_dm_broker_activated_tasks

    remove private key;

    go

    grant execute on usp_view_sys_dm_broker_activated_tasks to public

     

     

     

     

    Wednesday, August 15, 2007 10:13 AM

All replies

  • One can always use code signig to grant fine grained control to the needed funtionality to less priviledged users.

    Here is an example. While is not for the faint of heart, it works. The results are rock solid safe from security point of view. Your dba can create procedures for every needed view.

    Code Snippet

    use master

    go

    create procedure usp_view_sys_dm_broker_activated_tasks

    with execute as owner

    as

    select * from sys.dm_broker_activated_tasks;

    go

    create certificate sign_usp_view_sys_dm_broker_activated_tasks

    with subject = 'usp_view_sys_dm_broker_activated_tasks',

    start_date = '10/14/2007';

    go

    add signature to dbo.usp_view_sys_dm_broker_activated_tasks

    by certificate sign_usp_view_sys_dm_broker_activated_tasks;

    go

    create login login_sign_usp_view_sys_dm_broker_activated_tasks

    from certificate sign_usp_view_sys_dm_broker_activated_tasks;

    go

    grant authenticate server to login_sign_usp_view_sys_dm_broker_activated_tasks;

    grant view server state to login_sign_usp_view_sys_dm_broker_activated_tasks;

    go

    alter certificate sign_usp_view_sys_dm_broker_activated_tasks

    remove private key;

    go

    grant execute on usp_view_sys_dm_broker_activated_tasks to public

     

     

     

     

    Wednesday, August 15, 2007 10:13 AM
  • Thanks, I'll forward this to our DBA's

    Wednesday, August 15, 2007 1:58 PM