Generate a script to drop a DB user

1

I have a script below to search ALL DBs for a specific user:

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250))

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table
where username = 'frodo'
ORDER BY DBName

The top part of the image below is the result of the script. the bottom part is what i want to create based on the result.

image001 (2)

2

You heard about dbatools.io?

dbatools docs | Get-DbaDbUser

You can easily generatie what you want

Replace:
SELECT * FROM @DBuser_table

With:
SELECT CONCAT(''USE '', tbl.[DBName],'; ALTER ROLE ...'
FROM @DBuser_table tbl;

3

@RogierPronk thank you, worked.

never heard of dbatools. will take a look. thanks again