"Looping" through databases with sp_MSforeachdb and returning 1 data set

Question

So, I've been wrestling with the code I found on my buddy's website:

8 Steps to Moving Database Logins

I want to generate the Database Level Security, Roles, and Explicit Permissions statements in one output so I'm not copying and pasting over and over again and so that they run for all databases on the server (minus tempdb of course).

Declare @FullStatement varchar(MAX)
Set @FullStatement = ' use [?]; SELECT  dp.state_desc + N'' '' + dp.permission_name + N'' TO '' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500))  AS TSQLScript
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like ''##%'' -- excluds PBM accounts
and dpl.name not in (''dbo'', ''sa'', ''public'')
ORDER BY dp.permission_name ASC, dp.state_desc ASC'

Exec sp_MSforeachdb  @FullStatement

How can I modify what I have, which works as is but is inconvenient, using a Table Variable, Temp Table, etc so all of the statements are in one data set?

Answer 1

David,

Is this what you want?

CREATE TABLE tempdb.dbo.Results (c1 VARCHAR(8000))

Declare @FullStatement varchar(MAX)
Set @FullStatement = 'SELECT ''use [?]''; SELECT  dp.state_desc + N'' '' + dp.permission_name + N'' TO '' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500))  AS TSQLScript
FROM [?].sys.database_permissions AS dp
INNER JOIN [?].sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like ''##%'' -- excluds PBM accounts
and dpl.name not in (''dbo'', ''sa'', ''public'')
ORDER BY dp.permission_name ASC, dp.state_desc ASC'

INSERT INTO tempdb.dbo.Results Exec sp_MSforeachdb  @FullStatement

select * FROM tempdb.dbo.Results

Answer 2

There are multiple ways to get this done. You can use powershell to loop through all databases and put the results in excel. Mr Nelson has this on his powershell sql university series. Sorry, would pull the link for you but i am typing on my phone at the airport.

Answer 3

Why are you scripting database users and permissions? They are in the database and will still be there when you migrate the databases. You don't need to do that unless you're recreating the databases from scratch.