/ Sharepoint

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)? "

Name

User id

email address

System Group(s)

Date account created

Date deleted (for old accounts)

Fred Bloggs

user1

user@myDomain.com

Reader,
Approver

01/01/1999

N/A

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

Obtain a basic user report from Content DB
Share this

Subscribe to Cloud with a Sunny Outlook

Disclaimer: The software, source code and guidance on this website is provided "AS IS"
with no warranties of any kind. The entire risk arising out of the use or
performance of the software and source code is with you.

Any views expressed in this blog are those of the individual and may not necessarily reflect the views of any organization the individual may be affiliated with.