InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:22:15 GMT20http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxGreat script.2 things.1. Proper version handling.The version expresssion should be handled as this:CAST((LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)), 2)) AS INT)Then it will work for SQL 2008 and up2. Database names should be bracketed "[" and "]" to handle spaces and dashes in the database names.But the idea is awesome. It gives plenty of login/user/role info that is difficult to collect othewise.Good idea for a report too.ThanksAlex DonskoySQL Server DBA Greenberg & Trauriq PA, Miami FLMon, 31 Dec 2012 11:42:20 GMTaleksey donskoyhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx--minor changes so it works on a case-sensitive serverUSE masterGOBEGINIF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'begin IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser endendELSEbegin IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser endendCREATE TABLE #TUser ( DBName SYSNAME, [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name SYSNAME NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT)IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSforeachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid FROM [?].dbo.sysUsers u LEFT JOIN ([?].dbo.sysMembers m JOIN [?].dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in (''public'',''dbo'',''guest'',''sys'') ORDER BY u.name 'ELSE INSERT INTO #TUser EXEC sp_MSforeachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' and u.name not in (''public'',''dbo'',''guest'',''sys'') order by u.name 'SELECT *FROM #TUserORDER BY DBName, [Name], GroupNameDROP TABLE #TUserENDMon, 31 Jan 2011 08:48:19 GMTDarren-1054947http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHad a play around as well, and this runs on a Central Management Server across multiple SQL boxes with multiple builds (2000, 2005 & 2008).[code]USE MASTERGOBEGINIF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'begin IF EXISTS (SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser endendELSEbegin IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser endendCREATE TABLE #Tuser ( DBName SYSNAME, [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name SYSNAME NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT)IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid FROM [?].dbo.sysUsers u LEFT JOIN ([?].dbo.sysMembers m JOIN [?].dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in (''public'',''dbo'',''guest'',''sys'') ORDER BY u.name 'ELSE INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' and u.name not in (''public'',''dbo'',''guest'',''sys'') order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code]Thu, 28 Oct 2010 05:53:01 GMTAndeavourhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHiya, I've been playing around with this today as I have SQL2000 and I have made it work using the following attached codeKind regardsJo WrightThu, 28 Jan 2010 06:46:30 GMTjo.wrighthttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]FreeHansje (7/8/2009)[/b][hr]It might be a great script, but I still have the errors mentioned in the first posting; I thought it was rectified. I cannot find an easy way to remove these 'illegal' characters, hence I cannot test it. Pity[/quote]Please find the attached file and you should be able to run it. Issue is, when you copy code from the webpage, it brings in all the preceeding spaces as misc' charecters. Thanks,ShivaThu, 09 Jul 2009 08:20:39 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxIt might be a great script, but I still have the errors mentioned in the first posting; I thought it was rectified. I cannot find an easy way to remove these 'illegal' characters, hence I cannot test it. PityWed, 08 Jul 2009 08:41:18 GMTFreeHansjehttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxNice... works great.Thanks,-AdamWed, 15 Apr 2009 15:23:03 GMTadam-368690http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]adam (4/15/2009)[/b][hr]Thanks!Could someone post a screenshot of the expected output of the script would be?I have run the script on 2008, no errors, but there is no output. The server has several databases with a couple of users. So not sure what the issue could be.[/quote]Hi Adam, I dont have access to a 2008 instance to test this code on. but I found the bug that was not letting the result show up. Also, Understand that this code doesn't make any changes, it just pulls existing information. The resultset was not showing up cause, the below IF statement was not TRUE for 2008(version 10.xx). The resultset will be a plain table in all the versions.If you look in the code, there are 2 place where I check for the Instance's product version number. I've updated them correctly, so that it works for 2000,2005 and 2008 as well. Please let me know if you have any questions. I changed the IF statement to the following:[code] IF (LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9' OR LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)), 2) = '10') IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUser [/code]Wed, 15 Apr 2009 12:17:56 GMTshiva challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxThanks!Could someone post a screenshot of the expected output of the script would be?I have run the script on 2008, no errors, but there is no output. The server has several databases with a couple of users. So not sure what the issue could be.Wed, 15 Apr 2009 11:58:24 GMTadam-368690http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]adam (4/6/2009)[/b][hr]Can someone post the modified script for 2008?[/quote]Hi Adam, I made changes to the code so that it would work on 2008 as well. Post back if you see any issues. I have attached the updated script. updated the if statement to include version 10.xxMon, 06 Apr 2009 10:37:05 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxCan someone post the modified script for 2008?Mon, 06 Apr 2009 10:17:20 GMTadam-368690http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHi Rubinasd,SID is a security identifier for the login, you can get the loginname from sys.syslogins table.Name is the UserName in the corresponding database, for the corresponding login.My take at arranging the script's output in the below fashion would be to create a "Pivot Table" in MS Excel spreadsheet and drop all the columns into "Row Area".Servername........database name..................database roles.........................users........database name..................database roles.........................usersThu, 26 Mar 2009 21:29:33 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxI HAVE ONE MORE QUERY SHIVARAM......The sysdatabases gives a column sid which is system id of the database creator. How can we modify this such that we get the userid of the database creator?? Do you know this???Tue, 24 Mar 2009 04:57:39 GMTrubinasdhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHi Shivaram!Thanks for the response. Its put me to some rest as i was scared to death yesterday after running the script. I code i excuted is more or less the same that is available in permission.txt file i had attached earlier. I did some light modification like removing spaces or so and it worked. But i didnot see any grid as it didnot display one. It only gave me the message as sent to you earlier. Could it be because of executing the DROP #TUser table along with the script???? Please advice.....Can i get a script / if you could modify your script such that i get something like this...?Servername........database name..................database roles.........................users........database name..................database roles.........................usersMany thanks Shivam....Mon, 23 Mar 2009 23:11:33 GMTrubinasdhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHi, I can assure you that the script wouldn't make any changes. Can you post the latest script you have, so that I can take a look at whats going on.BTW, the script does just exactly that, Display a grid with the information.Mon, 23 Mar 2009 09:18:42 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxFinally i got the script running, but it gave me mssg[font="Arial"]Msg 208, Level 16, State 1, Line 3Invalid object name 'New_Oil_Export.dbo.sysUsers'.(365 row(s) affected)(365 row(s) affected)[/font]I RATHER ACCEPTED A GRID THAT DISPLAYED ROLES AND MEMBERS IN THEM. PLEASE EXPLAIN WHAT HAPPENED AS I AM WORRIED NOW....... DID IT MAKE ANY SERIOUS CHANGES TO MY DATABASES??????Mon, 23 Mar 2009 02:40:03 GMTrubinasdhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHi shiva...Your query looks really helpful...only if i could get thir running. Attached is the script i modified a little. I am getting error messageMsg 102, Level 15, State 1, Line 34Incorrect syntax near '?'.Msg 102, Level 15, State 1, Line 60Incorrect syntax near '?'.PLEASE HELPMon, 23 Mar 2009 02:29:23 GMTrubinasdhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHi Matt. I glad that the script was useful. And Thanks for the suggestions. The changes you suggested are published.Wed, 11 Feb 2009 08:37:59 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxI like this script and find it useful for generating audit reports; however there are a few enhancements I would like to propose:1. Any SQL Server object with a hyphen will cause the script to fail, so the script should be altered to use square brackets [] to enclose any potential SQL Server objects with hyphens. For example, a SharePoint database could be named SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484. The hyphens in the DB name cause the script to fail unless it is referred to as [SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484].Alter the script as follows anywhere you see the DBName:2005FROM [b][?][/b].sys.database_principals uLEFT JOIN ([b][?][/b].sys.database_role_members metc.2000FROM [b][?][/b].dbo.sysUsers uLEFT JOIN ([b][?][/b].dbo.sysMembers m etc.2. Make the DBName column larger than varchar(50) and LEFT function the DBName in the select statement with the amount of characters you created the table with so your insert is guaranteed to work. As it stands right now if you have a DNName larger than 50 characters the script will failAlter the script as follows:--Create the temp table with a larger DBName columnCREATE TABLE #tuser (DBName VARCHAR([b]100[/b]),--******************************************************INSERT INTO #TUserEXEC sp_MSForEachdb'SELECT[b]LEFT(''?'', 100)[/b] as DBName,3. Add server name to the output. If you are running this for auditing purposes and you run this on several machines, you'd want the server name in the output for when you put all the report together.Alter the script as follows:CREATE TABLE #tuser ([b]ServerName VARCHAR(100),[/b]DBName VARCHAR(100),--******************************************************INSERT INTO #TUserEXEC sp_MSForEachdb'SELECT[b]LEFT(@@SERVERNAME, 100) as ServerName,[/b]LEFT(''?'', 100) as DBName,FROM [?].dbo.sysUsers uLEFT JOIN ([?].dbo.sysMembers m etc.Wed, 11 Feb 2009 08:22:01 GMTMatt Karp-286289http://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxVery Nice!! it works on 2008 as well. Good to know.Thanks for the info.Wed, 10 Dec 2008 14:40:32 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxGreat script. I removed the different tests for versions 8 and 9 and got it running in SQL Server 2008 ! Many thanks.Wed, 10 Dec 2008 07:45:26 GMTPierrot Heritierhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxI added a couple more features as well, to extend to object level permissions as well and check for orphaned logins. It's based of the main table your script creates when it derives the new tables.[code]--First section, slightly tweaked from original script in posting/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user: 1. Find this code and u.name like ''tester'' 2. Uncomment the code 3. Replace the Name ''tester'' with the username you want to search on. Resultset: DBName: Database name that the user exists in. Name: user name. GroupName: Group/Database Role that the user is a part of. LoginName: Actual login name, if this is null, Name is used to connect. default_database_name default_schema_name principal_id sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005. - Added code to use SysObjects in 2000 instead of sys.objects10/09/2008 Cathy Greenselder - for 2000, switched the SysUsers join to use "m.groupuid" instead of "m.memberuid" **/USE MASTERGOBEGINIF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserCREATE TABLE #tuser ( rownum int identity(1,1), DBName VARCHAR(50), [Name] varchar(128), GroupName varchar(128) NULL, LoginName varchar(128) NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb 'use ?; SELECT db_name(), u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM .dbo.sysUsers u LEFT JOIN ( .dbo.sysMembers m JOIN .dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in(''dbo'', ''guest'', ''INFORMATION_SCHEMA'') /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb 'use ?; SELECT db_name(), u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM .sys.database_principals u LEFT JOIN ( .sys.database_role_members m JOIN .sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN .sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' and u.name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'') /*and u.name like ''tester''*/ order by u.name 'SELECT Name "User", dbname, GroupName, LoginNameFROM #TUser order by Name--DROP TABLE #TUser -- not dropping now as needed for next two sections.ENDgo-------------------------------------- NEW SECTIONS-------------------------------------- This second section generates all object level permissionscreate table #Tobjectlevel( dbname varchar(128), owner varchar(128), object varchar(128), grantee varchar(128), grantor varchar(128), protecttype varchar(128), action varchar(128), [column] varchar(512))exec sp_msforeachdb 'use ?;insert into #TobjectLevel(owner, object, grantee, grantor, protecttype, action, [column]) exec sp_helprotectupdate #TobjectLevel set dbname = db_name() where dbname is null'select grantee [User], dbname, object, action [Permissions] from #Tobjectlevel where grantee in (select Name from #TUser) order by upper(grantee) ascgo-- This section creates a list of orphaned users (i.e. those not in #TUser)create table #TOrphans( [User] sysname, dbname sysname)exec sp_msforeachdb 'use ?; insert into #TOrphans select name, db_name() from sysusers where name not in (select name from #TUser) and name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'') and roles <> 0x00'select * from #TOrphansgodrop table #TOrphansdrop table #Tobjectleveldrop table #TUser[/code]Tue, 11 Nov 2008 12:35:28 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxI am just glad that it is useful :)Tue, 11 Nov 2008 12:14:51 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxThis script is a lifesaver. It keeps our Loss Prevention folks happy when they need to audit any servers.Tue, 11 Nov 2008 10:39:03 GMTGabyYYZhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxCan you post the script that you've got after removing the 2000 part out? I tried doing the same and it worked with out any problem. try doing it like this (All i am doing here is enclosing the 3 part names in square parentheses):[code] FROM [?].[sys].[database_principals] u LEFT JOIN ([?].[sys].[database_role_members] m JOIN [?].[sys].[database_principals] r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].[sys].[server_principals] l ON u.sid = l.sid[/code]Replace the code in the [b]"from"[/b]clause with this. See if you have any spaces in the name of the database.Thu, 30 Oct 2008 08:26:47 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxI must be the only one who still can't get this to work properly. I am using SQL 2005 so the issues specific to SQL 2000 don't apply. I've removed all parts that are for SQL Server 2000 and removed the "IF THEN" items so that the only parts left are the call to sp_MSForEachDB and the INSERT done via the SELECT and while the thing does run, after going through a few DB's on the server it eventually retruns the erro message:Msg 102, Level 15, State 1, Line 10Incorrect syntax near '.'.I can't seem to get it to not return this error. I even ran the thing manually on each DB, removing the use of sp_MSForEachDB to try and determine which DB is the culprit but when I do that I no longer get any errors. GO figure?Anyone else have a similiar problem?Wed, 29 Oct 2008 16:28:07 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxCathy, thanks for working on it. here is the updated script:[code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005. - Added code to use SysObjects in 2000 instead of sys.objects10/09/2008 Cathy Greenselder - for 2000: switched the SysUsers join to use "m.groupuid" instead of "m.memberuid" **/USE MASTERGOBEGINIF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM ?.sys.database_principals u LEFT JOIN (?.sys.database_role_members m JOIN ?.sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN ?.sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code]Thu, 09 Oct 2008 08:30:53 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxAh ha! The center part of the 2000 join should be: FROM HISAppt.dbo.sysUsers u LEFT JOIN (HISAppt.dbo.sysMembers m JOIN HISAppt.dbo.sysUsers r ON m.groupuid = r.uid) -- groupuid not memberuid ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sidThat fixes the group thing.Thu, 09 Oct 2008 07:56:47 GMTCAGreensfelderhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxNo idea. I'm not real sure what the groups are supposed to look like. Someone who is actually a 2000 dba will have to look at it. I'm a developer and my database gets restored from a copy from production. Most of the users from the prod database don't have logins on my dev server so my results are mostly garbage. BTW you can get the default db on SQL2K with: l.dbname AS Default_db_Name, On the first go, I just nulled it out because I hadn't looked at it closely enough to find the 2000 equivalent of the 2005 field. I don't think 2000 has schemas.Thu, 09 Oct 2008 07:35:30 GMTCAGreensfelderhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]shiva challa (10/8/2008)[/b][hr]Here is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects[hr][code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005. - Added code to use SysObjects in 2000 instead of sys.objects**/USE MASTERGOBEGINIF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8 IF EXISTS ( SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM ?.sys.database_principals u LEFT JOIN (?.sys.database_role_members m JOIN ?.sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN ?.sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code][/quote]After I run the script the UserName and the GroupName are the same.Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.Please advise.ThanksWed, 08 Oct 2008 15:31:09 GMTKDASQLhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHere is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects[hr][code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005. - Added code to use SysObjects in 2000 instead of sys.objects**/USE MASTERGOBEGINIF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8 IF EXISTS ( SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM ?.sys.database_principals u LEFT JOIN (?.sys.database_role_members m JOIN ?.sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN ?.sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code]Wed, 08 Oct 2008 13:01:27 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]KDASQL (10/8/2008)[/b]Getting this error when I run this script in sql 2000:Server: Msg 208, Level 16, State 1, Line 4Invalid object name 'Tempdb.sys.objects'.[/quote]This is happening because there is no sys.objects in SQLServer 2000Replace the first IF statement with this, it wil work:[code] IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' IF EXISTS ( SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUser[/code]Apologise for this over look. I dont have a SQL 2000 instance for testing.Wed, 08 Oct 2008 12:50:40 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]shiva challa (10/8/2008)[/b][hr]Here is the updated script. I have also updated the online script, Just waiting for it to be published. [code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.**/USE MASTERGOBEGINIF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM ?.sys.database_principals u LEFT JOIN (?.sys.database_role_members m JOIN ?.sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN ?.sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code][/quote]Getting this error when I run this script in sql 2000:Server: Msg 208, Level 16, State 1, Line 4Invalid object name 'Tempdb.sys.objects'.Please advise.ThanksWed, 08 Oct 2008 11:55:19 GMTKDASQLhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]CAGreensfelder (10/2/2008)[/b][hr]Adapted for SQL 2000[code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 9/2/2008 Cathy Greenselder - Convert to SQL2000        (default_database_name not in SQL2K)        (default_schema_name    not in SQL2K)        (principal_id not in SQL2K)uid is in SQL2K**/USE MASTERGOBEGINIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].#TUser') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), UserName SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, uid INT, sid VARBINARY(85))INSERT INTO #TUserEXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sidWHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'SELECT *FROM #TUserORDER BY DBName, UserName, GroupNameDROP TABLE #TUserEND[/code][/quote]After I run the script the UserName and the GroupName are the same.Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.Please advise.ThanksWed, 08 Oct 2008 11:52:54 GMTKDASQLhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxHere is the updated script. I have also updated the online script, Just waiting for it to be published. [code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 09/02/2008 Cathy Greenselder - Convert to SQL2000 (default_database_name not in SQL2K) (default_schema_name not in SQL2K) (principal_id not in SQL2K) (uid is in SQL2K10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.**/USE MASTERGOBEGIN IF EXISTS ( SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), [Name] SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, default_database_name VARCHAR(50) NULL, default_schema_name VARCHAR(256) NULL, Principal_id INT, sid VARBINARY(85))IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id, u.sid FROM ?.sys.database_principals u LEFT JOIN (?.sys.database_role_members m JOIN ?.sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN ?.sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' /*and u.name like ''tester''*/ order by u.name 'SELECT *FROM #TUserORDER BY DBName, [name], GroupNameDROP TABLE #TUserEND[/code]Wed, 08 Oct 2008 07:57:20 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]shiva challa (10/2/2008)[/b][hr]Thanks Cathy !!Do you mind if I add this to the online script?[/quote]Not at all. Please do. :)CathyWed, 08 Oct 2008 06:57:26 GMTCAGreensfelderhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxThanks Cathy !!Do you mind if I add this to the online script?Thu, 02 Oct 2008 14:44:52 GMTshivaram challahttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxAdapted for SQL 2000[code]/**Script: list all Usernames, Roles for all the databases.Author: Shiva Challa (http://challa.info)and the database Roles that the user belongs to in all the databases. Also, you can use this script to get the roles of one user in all the databases. Directions of Use:For All Users list: You can directly run this script in SQL Server Management studioFor a specific user:        1. Find this code and u.name like ''tester''        2. Uncomment the code         3. Replace the Name ''tester'' with the username you want to search on. Resultset:        DBName: Database name that the user exists in.        Name: user name.        GroupName: Group/Database Role that the user is a part of.         LoginName: Actual login name, if this is null, Name is used to connect.        default_database_name        default_schema_name            principal_id        sidChange History:8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement. 9/2/2008 Cathy Greenselder - Convert to SQL2000        (default_database_name not in SQL2K)        (default_schema_name    not in SQL2K)        (principal_id not in SQL2K)uid is in SQL2K**/USE MASTERGOBEGINIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].#TUser') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE #TUserCREATE TABLE #tuser ( DBName VARCHAR(50), UserName SYSNAME, GroupName SYSNAME NULL, LoginName SYSNAME NULL, uid INT, sid VARBINARY(85))INSERT INTO #TUserEXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, u.uid, u.sid FROM ?.dbo.sysUsers u LEFT JOIN (?.dbo.sysMembers m JOIN ?.dbo.sysUsers r ON m.memberuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sidWHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1 /*and u.name like ''tester''*/ ORDER BY u.name 'SELECT *FROM #TUserORDER BY DBName, UserName, GroupNameDROP TABLE #TUserEND[/code]Thu, 02 Oct 2008 07:52:22 GMTCAGreensfelderhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspxYou can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online). systable in 2000 = view in 2005-------------------------------syslogins = sys.server_principals sysusers = sys.database_principals sysmembers = sys.database_role_members Thank you.:)Thu, 25 Sep 2008 09:51:25 GMTKDASQLhttp://www.sqlservercentral.com/Forums/Topic540661-1348-1.aspx[quote][b]KDASQL (8/28/2008)[/b][hr]Is there anyway we can make this work in SQL 2000. I need to run this on a SQL 2000 box.Please advise.Thank you.[/quote]You can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online). [code]systable in 2000 = view in 2005-------------------------------syslogins = sys.server_principals sysusers = sys.database_principals sysmembers = sys.database_role_members [/code]Thu, 25 Sep 2008 09:14:48 GMTshivaram challa