Cross Join error

Transact-SQL
1

Hi experts,
I have what is for me, a fairly complex query:

exec sp_msForEachDb ' use [?]
INSERT into DatabaseInventory.dbo.LoginsRoles
(ServerName, DBName,Role, Login)

select srv.server_name, db_name(), r.[name], p.[name]

from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
where r.name = "db_owner"
Cross join (
select name as server_name
from master.sys.servers
where server_id = 0
) as srv'

It gives 2 errors:
Incorrect syntax near the keyword 'Cross' and Incorrect syntax near the keyword 'as'
Thanks for any tips!

2

The CROSS JOIN must come before the WHERE:

exec sp_MsForEachDb ' use [?]
INSERT into DatabaseInventory.dbo.LoginsRoles
(ServerName, DBName,Role, Login)

select srv.server_name, db_name(), r.[name], p.[name]

from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
Cross join (
select name as server_name
from master.sys.servers
where server_id = 0
) as srv
where r.name = ''db_owner''
'
1 Like
3

Scott, this is exactly what I needed. It works perfectly. Thank you.