1

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 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 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?

  • Have you considered an stored procedure executed as? – McNets May 22 '18 at 9:20
  • @McNets That is even worse than a multi-step table-valued function because it cannot be called from queries at all. – GSerg May 22 '18 at 9:48
  • TRUSTWORTHY isn't a requirement for DB_CHAINING. It's only needed when a module (e.g. stored proc) contains EXECUTE AS <user> and the security context must be extended to other databases. Module signing is a better solution for that use case. Server-level permissions like VIEW SERVER STATE are granted to logins, not users, so you don't need to create a user in master. Why use a mult-statement TVF when the view can be encapsulated in an in-line TVF? – Dan Guzman May 22 '18 at 11:58
  • @DanGuzman Multi-step TVF is because with execute as cannot be applied to an inline TVF. I do need to create users in master because with the VIEW SERVER STATE permission they have full access to all management views and to stop that, I need to apply database-level DENY permissions to all views they should not see, like it is explained in the docs. – GSerg May 22 '18 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.