How to Retreive My Sharepoint Sites Links in a Publishing Site

The main theme of this article is to retrive the list of sites to which a user belongs. In a publishing sharepoint site , you have a hyperlink called “My Links”at the top right side of the site, when you click on this link you will get a drop down menu for “My Sharepoint Site”. If you are not getting this then you have not been added to the sites member group. After verifying if you find that you exist in a Sites member group and still the My Sharepoint Sites links are not getting populated in that case wait for few hours or a day. Microsoft has a time job which updates a users My Sharepoint Sites.

I wanted to create a custom menu where I can show a list of site to which a user belongs. There are 2 different approach

1 Is to loop through site collection and check the user membership and permission, if he his authorized then store it in some array list

2.Is try to find out how Microsoft is able to populate My Sharepoint List and follow the same.

I searched a lot on this but found very few article. Paul Liberand as a written a very good blog on this

http://liebrand.wordpress.com/2007/11/29/moss-2007-and-user-memberships/

The Approach which I am discussing is similar.I createa a stored procedure to retrive a list of site to which a user belongs. This stored procedure will be placed in a content database of Shared Service Provider on which the site is hosted

CREATE PROCEDURE [dbo].[proc_MySharePointSites]
(
@userName VarChar(400)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RecordId int
SELECT @RecordId = RecordId FROM dbo.UserProfile_Full WHERE NTNAME = @userName;

EXEC dbo.QuickLinksRetrieveAllItems @RecordId,@ViewerItemSecurity=31,@RequestedItemSecurity=16

END

QuickLinksRetrieveAllItems: It’s a stored procedure defined by microsoft which retrives Quick Links For My Sharepoint Sites. The main input parameter for this stored procedure is the RecordID which is unique for a user in a ssp(please correct me if I am wrong)

dbo.UserProfile_Full : This table consist of list of use in a ssp. You can retrieve a Record ID of any user from dbo.UserProfile_Full table by passing email id, login name or domain name of a user. Once you get a RecrodId you can pass this to the stored procedure proc_MySharePointSites create above and you can easily retrieve a list of a site to which a user belongs.

You can locate the table dbo.UserProfile_Full and the store QuickLinksRetrieveAllItems in the content database of a ssp, which you can open through SQL Server 2005 Management Studio

You can execute the stored procedure and get the list of site to which a user belongs programmatic ally

This is a workaround but not a right approach has it deal with SSP Content database