Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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:

http://www.mssqltips.com/tip.asp?tip=1718

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

1 Answer 1

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

 
discard

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.