SQLServer (2000 & 2005) query for security/role info

Question

I need a query to display information about the users, roles and privileges within an SQLServer 2000 and SQLServer 2005 database?

Answer 1

The logins are here:

SQL Server 2000: select * from master.dbo.syslogins

SQL Server 2005: select * from master.sys.logins

Users (per current database):

SQL Server 2000: select * from dbo.sysusers

SQL Server 2005: select * from sys.users

From Books Online:

How do I find all the owners of entities contained in a specified schema? [Before you run the following query, replace <database_name> and <schema_name> with valid names].

USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO