I want to give everyone access to a very limited portion of the dynamic management views. That is, I want everyone in my database to be able to select from this view:
create view dbo.KnowYourself as select net_transport, protocol_type, client_net_address from sys.dm_exec_connections -- requires VIEW SERVER STATE permission where session_id = @@SPID;
dm_exec_connections is a management view in the
I make sure that
dbo.KnowYourself belongs to
sa and that
sa maps to the same user in my database and in
master. I make sure both
master and my database have
DB_CHAINING ON, and are trustworthy just in case, even though it should not matter.
Still, no one can select from the view because they lack the
VIEW SERVER STATE permission.
I am aware of two workarounds:
Turn the view into a multi-step table-valued function and assign
with execute as ownerto it.
This does not work for me because certain queries significantly drop in performance when they have to call a multistep or scalar function instead of an inline function or a view (three seconds vs two hours).
Give all my users
VIEW SERVER STATEpermission, create them all in the
masterdatabase and assign them
DENYpermission on all dynamic management views except the one I need.
That may work, but concerns me as a management burden going forward (I will have to do that for each new user, and I will have to keep denying new management views as they appear in next versions of SQL Server).
Is there a better way to give any user ability to select from this view?