Home / server / Questions / 121444
Accepted
atricapilla
Asked: 2010-03-12 03:26:31 +0800 CST

SQL Server: how to check securables

  • 772

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];
sql-server sql-server-2005 security

1 Answers

  1. Best Answer

    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.

    • 2