Re: How can I see what permissions "VIEW SERVER STATE" has?

(pshroads@xxxxxxxxx) writes:
The problem is that I want to grant some developers access to certain
dynamic management views in production such as
sys.dm_exec_query_stats. But before I grant them VIEW SERVER STATE
permissions I want to know what else the will be able to see with
those permissions. There is no guarantee that that those 119 articles
cover all the VIEW SERVER STATE permissions. There should be a command
to explicitly list what VIEW SERVER STATE gives you access to.

Isn't the number 119 enough to give you a hint? On in three words: don't do
it.

Instead wrap dm_exec_query_stats in a view, or a table-valued function
that you sign with a certificate. Then you create a login to that
certificate, and grant that login VIEW SERVER STATE. Then you grant
access to the devs you want to see the query_stats data access to the
view/function.

Some notes:

1) A view is probably the most desireable, but I'm not sure whether that
will work. You may need a multi-statement UDF. (An inline UDF is the
same as a view.)

2) The login is not one that actually can login. It's just an appendix
to the certificate.

3) I have a much more detailed description on certificate signing on
my web site: http://www.sommarskog.se/grantperm.html.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: How can I see what permissions "VIEW SERVER STATE" has?
    ... The problem is that I want to grant some developers access to certain ... But before I grant them VIEW SERVER STATE ... those permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: is it possible deny sp_who and sp_who2 in 2005 ?
    ... own connection information unless you've granted VIEW SERVER STATE. ... To deny to a specific user, add the user to master and then deny execute ... permissions on the procs. ... You could instead revoke execute permissions from ...
    (microsoft.public.sqlserver.security)
  • Re: dbackupoperators
    ... You could create a user in master for the login and grant EXEC permissions on the xp to that user. ...
    (microsoft.public.sqlserver.server)
  • Re: EXECUTE AS question
    ... However, it only returns one record, for the current session only ... (as it would for a user without the VIEW SERVER STATE permission). ... An alternative solution is to sign the procedure with a certificate, ... and then associate create a login for the certificate, ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE AS question
    ... However, it only returns one record, for the current session only ... (as it would for a user without the VIEW SERVER STATE permission). ... and then associate create a login for the certificate, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)