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;'
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
This is a beautiful solution, yosiasz! Thank you very much
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