Report GUEST user access status in all databases on SQL instance level

• t2sqldba

  SSChasing Mays

  Points: 625

  More actions

  April 17, 2015 at 4:52 am

  #303048

  Hi,

  Below query tells us if guest user is enabled or disabled in a particular database

  SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'

  FROM sys.database_principals dp

  LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions

  WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms

  ON dp.principal_id = perms.grantee_principal_id

  WHERE dp.name = 'guest';

  Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name

• t2sqldba

  SSChasing Mays

  Points: 625

  More actions

  April 17, 2015 at 6:36 am

  #1791651

  All,

  FYI...

  I got my query solved. We can use DB_NAME() function.

  Thanks..:)