GUEST account and cmdexec

pma111
pma111 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
check this code from "how to find guest account is enabled or disabled sql server"
https://sites.google.com/site/jayantdass/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
CREATE TABLE #tuser (
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

Commented:
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
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/31/inf-sql-security-restricting-access-to-public-on-server-database-objects-its-implications-and-ownership-chains.aspx

---You can use the following query to check server permissions for public role,

SELECT
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

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