SharePoint – Using PowerShell to query the userinfo table

  • [アーティクル]

If you are a SharePoint engineer that needs to collect the user's information for a site collection straight from the content database and you don't have access to SQL. You can use a PowerShell script like below to query the content database for your site collection and all its users. It will provide you with the list of users from the site collection's userinfo table along with the authentication format of the user's account. This will help you troubleshoot permissions issues with users that have been added incorrectly or a failed migration.

Note - To run the script, update the site collection in Yellow below with the site collection you are troubleshooting. It will output the data to the screen and to a file called userinfo.txt in the local path.

 

############################################################################### # This script will return all the users in the Site collection's userinfo table
# This output will show the user accounts and their authentication format
# Update the site collection below with your site collection
###############################################################################
$sitecollection = "https://www.contoso.com/sites/site1"
###############################################################################
Add-PSSnapin "Microsoft.SharePoint.PowerShell" $site = get-spsite $sitecollection
$siteid = $site.ID
$DBServer = $site.Contentdatabase.server
$Database = $site.Contentdatabase.name
function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}
# File output to UserInfo.txt in the local folder
Run-SQLQuery -SqlServer $DBServer -SqlDatabase $Database -SqlQuery "SELECT [tp_Login],[Tp_Title],[tp_Email],[tp_deleted] FROM Userinfo where tp_SiteID like '$siteid'" | out-file UserInfo.txt -append
# Screen output
Run-SQLQuery -SqlServer $DBServer -SqlDatabase $Database -SqlQuery "SELECT [tp_Login],[Tp_Title],[tp_Email],[tp_deleted] FROM Userinfo where tp_SiteID like '$siteid'"
##############################################################################
 

Comments

  • Anonymous
    February 27, 2018
    I like it