List all users rolls for each database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

List all users rolls for each database

Author

Topic

MediaPirate
Yak Posting Veteran

52 Posts

Posted - 2004-12-29 : 11:31:15

I haven't had much luck getting any help with this request anywhere else so I'm hoping maybe someone can help here. I'm running an audit and saving the output in an Excel sheet for our Auditors. They basically requested a listing of all databases, users who have access to each database and the role they're assigned (DBO) Read or Write access. I can get all the users for each database using :

exec sp_msforeachdb "use ? select'?',name from sysusers where islogin=1 and uid not in(1,2) and'?'not in('master','msdb','tempdb','model','admindb')"

No problem, but I would like to add what rolls each user has on that database in the same query. If anyone can help I would greatly appreciate it.

Thanks, Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 13:01:34

This should work:

SELECT u1.[name], u2.[name]
FROM sysusers u1
INNER JOIN sysmembers m
ON u1.uid = m.memberuid
INNER JOIN sysusers u2
ON m.groupuid = u2.uid
WHERE u1.uid <> u1.gid AND u1.[name] <> 'dbo'
ORDER BY u1.[name]

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources