Is it possible to work around the need to have a sever level permission with ownership chaining or the sort?

by GSerg   Last Updated May 22, 2018 10:06 AM

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

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.

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 owner to 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 STATE permission, create them all in the master database and assign them DENY permission 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?



Related Questions


change schema ownership from a user to dbo

Updated June 16, 2018 21:06 PM



How to script out the grant view on login permission?

Updated September 21, 2018 17:06 PM