My Environment has both SQL server 2000 and SQL server 2005. Does some one has a script to map out all the logins and all the users for each database. This is for a security audit. Any help is appreciated.

For SQL 2000 this will list all users, their permssions and the roles to which the user belongs. Probably more than you require, so modify to suit your exact needs.Original T-SQL found on SCC a long time ago and unfortunately I did not record the name of the individual who posted same in a forum. To that unknown individual I say thank you.

CREATE PROCEDURE UDP_User_Permissions ASSET NOCOUNT ON --Don't want all the counts from the process to return--Check for and drop our temp table if exists--Create our temp work table to make sure we have all the inheritanceCREATE TABLE #tmpInher ( [qid] [int] IDENTITY (1,1) NOT NULL, [user] [int] NOT NULL, [inherfrom] [int] NOT NULL, PRIMARY KEY ([user], [inherfrom] ))--Insert the inheritance base items which are the users themselves.INSERT INTO #tmpInher ([user], [inherfrom]) SELECT [uid], [uid] FROM sysusers WHERE issqlrole = 0 AND hasdbaccess = 1 and uid != 1--Loop thru until we get all the inheritance items that a user is associated with.WHILE EXISTS (SELECT oT.[user], groupuid From sysmembers Inner Join #tmpInher oT ON oT.[inherfrom] = sysmembers.memberuid Where groupuid NOT IN ( SELECT inherfrom From #tmpInher iT Where iT.[User] = oT.[User] ) )BEGIN INSERT INTO #tmpInher ([user], [inherfrom]) SELECT oT.[user], groupuid From sysmembers Inner Join #tmpInher oT ON oT.[inherfrom] = sysmembers.memberuid Where groupuid NOT IN ( SELECT inherfrom From #tmpInher iT Where iT.[User] = oT.[User] )End--Check permissions for the user from all inheritance paths.SELECT u2.[name] AS UserName, u1.[name] AS InheritesVia, --Role name which has the permission Case xtype WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' WHEN 'S' THEN 'System' WHEN 'P' THEN 'Procedure' WHEN 'FN' THEN 'Function' END AS ObjectType, sysobjects.[name] AS Object, CASE WHEN xtype IN ('U','V','S') THEN CASE WHEN (actadd & 1) = 1 THEN 'Granted' WHEN (actmod & 1) = 1 THEN 'Denied' Else 'Revoked' End Else '' END AS [SELECT], CASE WHEN xtype IN ('U','V','S') THEN CASE WHEN (actadd & 8) = 8 THEN 'Granted' WHEN (actmod & 8) = 8 THEN 'Denied' Else 'Revoked' End Else '' END AS [INSERT], CASE WHEN xtype IN ('U','V','S') THEN CASE WHEN (actadd & 2) = 2 THEN 'Granted' WHEN (actmod & 2) = 2 THEN 'Denied' Else 'Revoked' End Else '' END AS [UPDATE], CASE WHEN xtype IN ('U','V','S') THEN CASE WHEN (actadd & 16) = 16 THEN 'Granted' WHEN (actmod & 16) = 16 THEN 'Denied' Else 'Revoked' End Else '' END AS [DELETE], CASE WHEN xtype IN ('P','FN') THEN CASE WHEN (actadd & 32) = 32 THEN 'Granted' WHEN (actmod & 32) = 32 THEN 'Denied' Else 'Revoked' End Else '' END AS [EXEC], CASE WHEN xtype IN ('U','V','S') THEN CASE WHEN (actadd & 4) = 4 THEN 'Granted' WHEN (actmod & 4) = 4 THEN 'Denied' Else 'Revoked' End Else '' END As DRIFrom syspermissionsInner Join #tmpInher Inner Join sysusers u1 ON u1.uid = [inherfrom] Inner Join sysusers u2 ON u2.uid = [User]ON [inherfrom] = granteeInner Join sysobjectsON sysobjects.[ID] = syspermissions.[ID]Order By [UserName], [ObjectType], [Object]--Drop out temp table as we no longer need.DROP TABLE #tmpInher

If everything seems to be going well, you have obviously overlooked something.

This script worked for me when I needed to script out the logins for each database, this script is using a stored procedure which you can find at the following URL: http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/