The Requirement was to provide a report to list all users with db_owner role. No big deal right? But the fun part was there were many servers running SQL 2000 version. Of course first thing first, search in the internet. Got few but not quite which I was looking for.
So ultimately I wrote the below query to provide the required details in SQL 2000. I have kept the option open to list not only for db_owner but also for any other database role.
You can tweak it as per your requirements.
SELECT p.name AS UserName,
CASE WHEN p.isntgroup =1 AND p.isntuser=0 THEN 'Windows Group'
WHEN p.isntgroup =0 AND p.isntuser=1 THEN 'Windows Login'
WHEN p.issqlrole=1 THEN 'Database Role'
ELSE 'SQL Login' END AS 'Login Type',
USER_NAME(roles.groupuid) AS 'AssociatedRole'
FROM sysmembers roles
JOIN sysusers p ON roles.memberuid = p.uid
JOIN sysusers pp ON roles.groupuid = pp.uid AND p.uid NOT IN (0,1,2,3,4) AND
USER_NAME(roles.groupuid) IN ('db_owner', 'db_datareader')
The following screen shot shows the output:
I hope this helps. This post can only be treated as complete once you put your thoughts in the comment section.
You might also want to check the link which includes video to know the Myth around Remote Query Timeout Option
