Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
-- How to find Guest account is enabled or disabled
-- run the below script
CREATE TABLE #tuser (
DBName sysname NULL ,
hasdbaccess sysname NULL
EXEC sp_MSforeachdb '
SELECT ''[?]'' AS DBName,name as UserName , hasdbaccess FROM sysusers where name= ''guest'' ;'
select DbName, UserName , case hasdbaccess when '1' then 'Enabled' else 'Disabled' end as Status from #tuser
Open in new window
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
---You can use the following query to check server permissions for public role,
State_Desc, Permission_Name, class_desc,
COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
where Grantees.Name = 'public'
ORDER BY SecurableName
From novice to tech pro — start learning today.
Members can enroll in this course at no extra cost.