GUEST account and cmdexec

pma111 used Ask the Experts™
I need an SQL for 2008 R2 to list all databases on an instance, and whether the GUEST account is enabled in these DB or not.

Also I need a way to determine if cmdexec is available to the PUBLIC role, ideally via a query.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
check this code from "how to find guest account is enabled or disabled sql server"
Q#1 from your post: find Guest account

-- How to find Guest account is enabled or disabled  
-- run the below script
DBName sysname NULL ,
[UserName] sysname, 
hasdbaccess sysname NULL

INSERT #tuser
 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

please clarify Q#2
Do you mean cmdexec in the context of a SQL Agent job? Or xp_cmdshell extended proc

check for now

MS Article
INF: SQL Security - Restricting access to public on server/database objects, its implications and ownership chains

---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

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

pma111, do you still need help with this question?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial