SQLTeam.com | Weblogs | Forums

CTE Syntax Error

#1

Hi experts, I'm running this against a CMS node to collect logins, roles across all the registered servers.
I'm getting Incorrect syntax near the keyword 'exec'. and
Incorrect syntax near 'db_owner'.

;with roles_cte as
(exec sp_MsForEachDb ' use [?]

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

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
)
INSERT into DatabaseInventory.dbo.LoginsRoles
(ServerName, DBName,Role, Login)
Select server_name,DBName,role,Login
from roles_cte
where rownum=1 AND role IN('db_owner')
order by role;'

#2
create table #LoginsRoles(server_name varchar(120), 
dbname varchar(120), role varchar(120), login varchar(120));

exec sp_MsForEachDb 'use [?]

;with roles_cte as
(
	select srv.server_name, db_name() as dbname, 
r.[name] as role, p.[name] as login

	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
)
insert into tempdb..#LoginsRoles
select * 
  From roles_cte
'

select  * From #LoginsRoles
1 Like
#3

This is a beautiful solution, yosiasz! Thank you very much

#4

can also be done without cte really

create table #LoginsRoles(server_name varchar(120), 
dbname varchar(120), role varchar(120), login varchar(120));

exec sp_MsForEachDb 'use [?]

insert into tempdb..#LoginsRoles
select srv.server_name, db_name() as dbname, 
r.[name] as role, p.[name] as login

	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

'

select  * From #LoginsRoles