November 30, 2007 at 9:54 am
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
December 5, 2007 at 3:23 pm
Hi Sandra,
I've had to do some similar stuff with this and one thing I found helpful was the sp_helpuser function. You should be able to create a cursor that takes the contains all the db's and users then run sp_helpuser for each one dumping the results to a temp table. Hope this helps!
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply