Complete a Server Assessment – Guest Account Status

Below you will find the code to gather information about the status of the Guest account for each database.  Simply select the text and copy to the Clipboard.

, pr.type_desc
, pe.permission_name
, pe.state_desc
, pe.Class_desc
, CASE WHEN pe.Class_desc <> ‘Schema’ THEN object_name(pe.major_id) ELSE ‘Is A Schema’ END AS ‘Object Name’
, AS ‘SchemaName’
FROM sys.database_principals pr
LEFT OUTER JOIN sys.database_permissions pe
ON pe.grantee_principal_id = pr.principal_id
LEFT OUTER JOIN sys.schemas sc
ON pe.major_id = sc.schema_id
WHERE = ‘guest’

, hasdbaccess
, CASE WHEN hasdbaccess = ‘0’ THEN ‘Inactive’ ELSE ‘Active’ END AS ‘GuestAccountStatus’
FROM sysusers
WHERE name =‘Guest’

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.