Obtain a basic user report from Content DB

 First up, you shouldn't read this posting. It details querying data from the Sharepoint Content Database directly. That can make your install unsupported, and I don't suggest you do it, ever. OK? Also I created a temporary function and Stored Procedure in there to do what I wanted. You should never ever do that either. Walk away from this posting now, it's just for information.

Still here? Wow, I was pretty stern there.

I got a request from an important stakeholder for one of my MOSS 2007 environments;

"Can this be extracted from the DB via a SQL query? If poss could you run a report and send it to me (rough and ready will do as I’ll tidy up)? "

How to do it;

The exact format wasn't possible. The date an account was deleted for instance isn't available.

The basic SQL to achieve this is this;

SELECT dbo.UserInfo.tp_Title AS Name, dbo.UserInfo.tp_Login AS [User ID], dbo.UserInfo.tp_Email AS Email, dbo.UserInfo.tp_IsActive AS Active,
dbo.Groups.Title AS [Group]
FROM dbo.Groups INNER JOIN
dbo.GroupMembership ON dbo.Groups.ID = dbo.GroupMembership.GroupId RIGHT OUTER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID
WHERE (dbo.UserInfo.tp_DomainGroup = 0)

This is unsatisfactory however as the one to many relationship of user to Groups created duplicate rows for users. So the query was split into a function to concatenate a users groups, and a stored procedure to generate the report. Source below;

USE [WSS_Content]
GO
/****** Object: StoredProcedure [dbo].[temp_UserReport] Script Date: 09/16/2008 17:07:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[temp_UserReport]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT tp_Title AS Name, tp_Login AS [User ID], tp_Email AS Email,
CASE dbo.UserInfo.tp_Deleted WHEN '0' THEN 'False' ELSE 'True' END AS [Is Deleted], dbo.fn_TempGetUserGroups(tp_ID) AS Groups
FROM dbo.UserInfo
WHERE (tp_DomainGroup = 0)
order by [Is Deleted], [Name]
END

USE [WSS_Content]
GO
/****** Object: UserDefinedFunction [dbo].[fn_TempGetUserGroups] Script Date: 09/16/2008 17:08:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[fn_TempGetUserGroups] (@MemberID int)

RETURNS varchar(2000)
AS
BEGIN
declare @bigstring varchar(2000)

declare @comma varchar(1)

set @bigstring = ''

set @comma = ''

select

@bigString = Rtrim(@bigString) + @comma + dbo.Groups.Title,

@comma = ','

FROM dbo.Groups INNER JOIN
dbo.GroupMembership ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId = @MemberID

Return @bigstring

END