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