Powershell in SSMS

I had a restored database of about 300GB and wanted to monitor its progress.  The Stats argument on the RESTORE command will give you some limited feedback but wanted something more.  This Powershell will do a running update every 10 seconds.

import-module sqlps

for(;;)

{

cls

invoke-sqlcmd -serverinstance apcgs0nhqu398mv\privweb -query "select SUBSTRING(text, charindex('[', text) + 1, charindex(']', text) - charindex('[', text) - 1) AS [DB], command, percent_complete from sys.dm_exec_requests cross apply sys.fn_get_sql(sql_handle) where command like 'restore%'"

start-sleep -seconds 10

}

ADFS 2.0 Token based Authentication Token Expiry

Token based Authentication Token Expiry

Tokens issued by AD FS 2.0 expire after a default time of 60 minutes. This requires users to be re-authenticated (for internal access) or to sign in again (for IFD access). The token lifetime is set separately for each relying party trust (internal and external).
To check the life time, complete the following steps on the AD FS 2.0 server:

1. Check the names for the relying party trusts in the AD FS 2.0 Management Console and use the appropriate names in the following steps.
2. Start Windows Powershell (use Run as Administrator).
3. In Powershell, type Add-PSSnapin Microsoft.Adfs.Powershell.
4. To view information for the internal relying party trust, type Get-ADFSRelyingPartyTrust –Name “CRM Internal Claims Relying Party”. Replace CRM Internal Claims Relying Party with the appropriate name for your ADFS configuration.
5. Review the value for TokenLifetime. The value is measured in minutes.
6. To change the value, type Set-ADFSRelyingPartyTrust –Targetname “CRM Claims Relying Party” –TokenLifetime newvalue where newvalue is the value in minutes.
7. Repeat this process for the external (IFD) relying party trust.

Source:

1. http://www.fkbase.info/node/163
2. https://technet.microsoft.com/en-us/library/gg188586(v=crm.6).aspx
3. https://samlman.wordpress.com/2015/03/01/setting-the-login-token-expiration-correctly-for-sharepoint-2010-saml-claims-users/
4. http://stackoverflow.com/questions/23692326/adfs-2-0-microsoft-identityserver-web-invalidrequestexception-msis7042

Powershell for SID from AD Group Name

Problem:

Had an issue with an Active Directory group.  My algorithm needed to specify the SID and all I had was the name of the group.  I needed a quick way to determine the SID.

Solution:

Get-AdGroup -filter {Name -eq "FCJC_Records_Admin"}

Source:

https://technet.microsoft.com/de-de/library/ee617196.aspx

Powershell cannot be loaded because execution of scripts is disabled on this system.

Everytime I need to run a powershell script I forget to set the ExecutionPolicy

Set-ExecutionPolicy Unrestricted

This will let you run the script without the error. Be warned you do this at the risk of it not being from a trusted source.

Get-ExecutionPolicy

This will tell you what your current session is running at.

Source:
http://technet.microsoft.com/en-us/library/ee176949.aspx

A service application database was deleted in SQL Server Management Studio

Solution
Using PowerShell, run the following command:

Get-SPDatabase | where {$_.exists -eq $false}

This will list all databases in SharePoint that no longer exist on the database server. If you are happy with the result and wish to remove the orphaned databases, run the following command:

Get-SPDatabase | where {$_.exists -eq $false} | foreach {$_.delete()}

Source:
http://www.mysharepointadventures.com/2012/02/cleaning-up-deleted-databases-in-sharepoint/