Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to make a t-sql query to check which logins have 'view server state' permission in server type securables. How to achieve this?

This query from mssqltips don't show this:

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission]; 
share|improve this question
up vote 2 down vote accepted

You need to check the server level permissions. The query you have checks permissions at the database level. You can achieve this by using the sys.server_permissions and sys.server_principals views. Something like:

SELECT  pr.*
FROM    sys.server_permissions p
    INNER JOIN sys.server_principals pr ON p.grantee_principal_id = pr.principal_id
WHERE   p.type = 'VWSS'

See this for more information about the sys.server_permissions view.

share|improve this answer
Thanks! Great!! – atricapilla Mar 11 '10 at 12:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.