SPS 2003 SQL Queries

readme.md

These scripts were created for use with SPS 2003/WSS 3.0

Copy the code you need Open SQL Management Studio Connect to the SQL Server Click 'New Query' Paste the code into the query/script pane (change the database name in the script as required) Click Execute Export the results as needed

ListSiteLastAccessDate.sql

List Sites (Top URL, Sub URL, Site Name, Last Accessed
*/
USE
	/* CHANGE THIS ---> */ DATABASENAME
SELECT
	dbo.Sites.FullUrl AS [Top URL],
	dbo.Webs.FullUrl AS [Sub URL],
	dbo.Webs.Title AS [Site Name],
	DATEADD(d,dbo.Webs.DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101)) AS [Last Accessed]
  
FROM
	dbo.Webs
	RIGHT OUTER JOIN
		dbo.Sites ON dbo.Webs.SiteID = dbo.Sites.ID

ListSiteUserPermission.sql

/* 
SQL Script to List Sites (Top URL, Sub URL, Site Name, User, Permission Group)
*/
USE
	/* CHANGE THIS ---> */ DATABASENAME
SELECT DISTINCT
	dbo.Sites.FullUrl AS [Top Site],
	dbo.Webs.FullUrl AS [Sub Site],
	dbo.Webs.Title as [Site Name],
	dbo.UserInfo.tp_Title AS [User Name],
	dbo.WebGroups.Title AS [Permission Group]	

FROM
	dbo.Webs
	RIGHT OUTER JOIN 
		dbo.WebGroups
	INNER JOIN
		dbo.WebGroupMembership ON dbo.WebGroups.WebID = dbo.WebGroupMembership.WebID AND dbo.WebGroups.ID = dbo.WebGroupMembership.GroupID
	INNER JOIN
		dbo.UserInfo ON dbo.WebGroupMembership.MemberID = dbo.UserInfo.tp_ID ON dbo.Webs.SiteId = dbo.UserInfo.tp_SiteID AND dbo.Webs.Id = dbo.WebGroups.WebID
	RIGHT OUTER JOIN
		dbo.Sites ON dbo.Webs.SiteID = dbo.Sites.ID