I am trayng to create a report that will give server role for each user on the server , but it also has what databases are maped to each one of those users.
The problem:
the query to get server roles needs to be run on master
but the query to see what databases are mapped to each user needs to be run on the individual database.
the only way i can think of doing this is to do a cursor that would insert into a temp table the list of users per database, and then join to the master database to get the server roles, however i do nto think that is possible to do in reporting services.
Any one has an idea of how to solve this problem within reporting services?
1. quey to get server roles (to be run on Master):
select @@servername as servername, s.[name], s.createdate, s.updatedate, s.denylogin as is_disabled, s.[hasaccess], s.[isntname], s.sysadmin, s.securityadmin, s.serveradmin, s.setupadmin, s.processadmin, s.diskadmin, s.dbcreator, s.bulkadmin, l.is_policy_checked, l.is_expiration_checked
from sys.syslogins s with (nolock)
JOIN sys.sql_logins l ON (s.name = l.name)
where s.isntname = 0
order by s.[dbname], s.[name]
2. query to see users maped to each database (to be run on the individual database):
select db_name(), b.name
from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner
join sys.database_principals c on c.principal_id = a.role_principal_id