I've not ever had to do this before.  I started at sys.schemas and worked by way up to the other two sys views but, like I said, I've not...

right, temp table is optional but i feel it's a cleaner look result.  whereas the grid is hard to see.  thank you

something like this:

CREATE TABLE #tempdblogin 
  (DBName nvarchar (100), 
  PrincipalName nvarchar(100),
  PrincipalType nvarchar(100),
  GrantedBy nvarchar(100),
  permission_name nvarchar(100),
  state_desc nvarchar(100),
 ...

SELECT prin.name, perm.permission_name
FROM sys.server_principals prin
INNER JOIN sys.server_permissions perm
ON prin.principal_id = perm.grantee_principal_id
WHERE prin.type = 'R' and prin.name = 'test'

Replace 'test' with the name of the role you...

hi jeff, i have a script to list all SPs that invokes xp_cmdshell.

our db security team has identify xp_cmdshell  as a security finding.  they want us to disable this xp.

as...

i found a script.  this can be close

thanks johan!

@johan - wow, alot to unpack here 🙂

i just join the team and relatively new to sql server/dba.  i should have said our current team backup practice, not me specifically. ...

• This reply was modified 2 years, 2 months ago by  lan2022nguyen.

no other server role beside public.  mystery to me

@grant - yes, member of public role.

Thanks so much Steve. it worked.

thanks sandwich.

Thanks Phil, worked! 🙂

The top part of this screenshot is what it showing up after i use that last select statement.  Notice the DBnamd keeps saying ABC.

the bottom portion is...

@steve-2 - the script to find the datawriter works fine.  however, i'd like to include another column to drop the members of this result.

this is what i have...

good question.

basically the database security audit team ran guardium scan and found a finding that say fixed db roles such as datawriter, datareader, etc must not be assigned directly to...