Monday, January 16, 2012

Get The DBO From All SQL Databases

I have finally decided to start keeping track of the useful SQL commands that I have used.  Mostly because I am tired of rewriting them.  Also, if I have had to use them, then I sure that someone else (or me again) might find them useful.
While moving databases around within SQL to optimize IOPS and/or drive utilization, you might have a need to put the Database Owner back to what it was originally. Before you drop your databases, take a look at the DBO first.
This will grab all the dbo's of all the databases on your server:
select SUSER_SNAME(owner_sid) as username, name from sys.databases
Now, if you want to change the DBO...
sp_changeDbOwner @loginame = 'domain\username'
However, you might run into an error is the DBO is already a user or aliased in the database.
To fix this problem, run the following:
USE <databaseName>
GO
SP_DROPUSER 'domain\username'
GO
SP_CHANGEDBOWNER 'domain\username'
UPDATE 02/04/2015
Added drop user and change owner code.

Tuesday, January 10, 2012

Add SharePoint Snap-In to PowerShell ISE

Let me start off with saying that I, in no way, came up with this solution.  It was first shown to me by Shannon Bray (http://shannonbray.wordpress.com) when he and Gary Lapointe were maintaining spPowerShell.com.
Since the site is no longer available, I have had to grab the following information from Kirk Evens (Add Microsoft.SharePoint.PowerShell Snap-In to All PowerShell Windows) and from Spence Harbar (Adding SharePoint 2010 PoweShell cmdlets to your PowerShell ISE).

Open up Windows PowerShell ISE (Run as Administrator), and run the following script:

# set the execution policy to run scripts
set-executionpolicy unrestricted -force

# Create the profile
if (!(test-path $profile.AllUsersAllHosts)) {new-item -type file -path $profile.AllUsersAllHosts –force}
psEdit $profile.AllUsersAllHosts
In the new Tab,
For SharePoint 2010 add the following:
cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration'
.\SharePoint.ps1
cd \ 
For SharePoint 2013 add the following:
cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\CONFIG\POWERSHELL\Registration'
.\SharePoint.ps1
cd \ 
Save the Profile tab (no, you do not run the script)

Again, thanks to Shannon, Kirk, Gary, and Spence for their posts!

UPDATE (01/16/2012)
After speaking with Shannon, he has finally moved over the blog...  you can find his ISE blog here...
http://shannonbray.wordpress.com/2010/06/23/sharepoint-and-powershell-ise/
UPDATE (10/21/2012)
Added the section for SharePoint 2013...