Monday, May 23, 2011

List All SCA in Site Collections in All Web Applications

-- Author: Barry I. Schneider
-- This script list all of the site collection administrators in all of the site collections
-- in all of the web application in the farm.

-- Site Collection Administrators are identieifed in the content database table UserInfo with
-- the field tp_SiteAdmin = 1

-- Script Design
-- Find all of the database servers and instances that are hosting content databases
-- Create ServerLink to support distributed queries
-- Get list of Web Applications, there Site Collections, and their assocated content databses
-- Loop through content databases and query for Site Collection Administrator
-- Drop ServerLink


Use SharePoint_Config
-- script level variables
set nocount on -- prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure
declare @i int --iterator
declare @iRwCnt int --rowcount
DECLARE @cmd NVARCHAR(1000)

-- variables for adding and dropping linked servers
DECLARE @dbServer nvarchar(128) -- sharepoint databasename
DECLARE @dbInstance nvarchar(128) -- web application hostname
DECLARE @Links TABLE (ID int identity(1,1), dbServer nvarchar(128), dbInstance nvarchar(128))


-- Create ServerLink to support distributed queries
set @i = 1 --initialize

-- I discovered that the ClassID for the isntance name is 3112E92F-B97D-481E-8CEB-03FDE15ED1A7
-- select the Names og the SQL Instance (Objects.Name) of all the records with ClassID of 3112E92F-B97D-481E-8CEB-03FDE15ED1A7
-- select the Names of the SQL server (Objects_1.Name) associated witht he instance
-- store them in a temp table called links
INSERT INTO @Links SELECT   Objects_1.Name AS Server, Objects.Name AS Instance
FROM         Objects INNER JOIN
                      Objects AS Objects_1 ON Objects.ParentId = Objects_1.Id
WHERE     (Objects.ClassId = '3112E92F-B97D-481E-8CEB-03FDE15ED1A7')

set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work
-- loop thru table instaed of using cusor
while @i <= @iRwCnt
begin
select @dbServer = dbServer from @Links where ID = @i
select @dbInstance = dbInstance from @Links where ID = @i

Print @dbServer
Print @dbInstance

DECLARE @lName nvarchar(128)
set @lName = @dbServer + @dbInstance
set @cmd = @dbServer +'\' + @dbInstance

EXEC sp_addlinkedserver  
   @server=@cmd,
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc=@cmd

EXEC sp_addlinkedsrvlogin @cmd, 'true'

--commit tran

set @i = @i + 1
end

-- Get list of Web Applications, there Site Collections, and their assocated content databses
-- Loop through content databases and query for Site Collection Administrator
set @i = 1 --initialize

DECLARE @wss_db nvarchar(256) -- sharepoint databasename
DECLARE @url nvarchar(128) -- web application hostname
DECLARE @sites TABLE (ID int identity(1,1), wssdb nvarchar(256), siteURL nvarchar(128))
DECLARE @siteSCA TABLE (SiteCollectionAdmin nvarchar(128), siteCollectionURL nvarchar(128), WebAppURL nvarchar(128))

INSERT INTO @sites SELECT DISTINCT CHAR(91) + b.Name + Char(92)+ F.Name +  CHAR(93)+ CHAR(46) +    a.Name   AS content_db, d.Name AS Siteurl
FROM         Objects AS a INNER JOIN
                      SiteMap AS c ON a.Id = c.DatabaseId INNER JOIN
                      Objects AS d ON c.ApplicationId = d.Id INNER JOIN
                      Objects AS F ON a.ParentId = F.Id INNER JOIN
                      Objects AS b ON F.ParentId = b.Id
WHERE     (a.Id IN
                          (SELECT     DatabaseId
                            FROM          SiteCounts))

set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work
-- this stopped working for unknown reason
--create clustered index idx_tmp on @site(ID) WITH FILLFACTOR = 100

while @i <= @iRwCnt
begin
select @wss_db = wssdb from @sites where ID = @i
select @url = siteURL from @sites where ID = @i

Print @wss_db
Print @url
Print SUBSTRING(@url, 14, LEN(@url))

--begin tran
SET @cmd = 'SELECT a.tp_Login, ''' + SUBSTRING(@url, 14, LEN(@url)) + char(47) + '''+ b.FullUrl, '''+@url+'''
FROM ' + @wss_db + '.dbo.UserInfo as a INNER JOIN
 ' + @wss_db + '.dbo.Webs as b ON a.tp_SiteID = b.SiteId
WHERE (a.tp_SiteAdmin = 1 and b.ParentWebId IS NULL)'
Print @cmd
insert into  @siteSCA  EXEC (@cmd) 
--commit tran

set @i = @i + 1
end

Select * from @siteSCA

-- Drop linked servers
set @i = 1 --initialize


while @i <= @iRwCnt
begin
select @dbServer = dbServer from @Links where ID = @i
select @dbInstance = dbInstance from @Links where ID = @i

Print @dbServer
Print @dbInstance

set @cmd = @dbServer +'\' + @dbInstance

EXEC sp_dropserver @cmd, 'droplogins';


--commit tran

set @i = @i + 1
end

Tuesday, May 17, 2011

SharePoint 2010 70-668 Study Guide

Plan for backup and recovery
Plan a SharePoint component strategy
Plan quota management
Use search administration reports
Workflows overview
Choose administrators and owners for the administration hierarchy
Hardware and software requirements
Software updates overview
Plan Web pages
Capacity management and sizing overview
Configure alert settings for a Web application
Deployment scenarios
Plan the topology for enterprise search
Plan for social computing and collaboration
Plan and deploy authentication methods
Plan for availability

Design a migration strategy
Plan and deploy authentication methods

Plan for a business intelligence strategy
Establish an enterprise monitoring plan

Introducing Records Management