On the Journey to be a SQL Server Professional: PowerShell Scripts based on Sean McCown's Videos

<############################################# How to script tables, USPs etc ##############################################>

#Scripting all tables

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> gci | %{$_.script()}

# How to script tables with options

# Step 1 - We need to load the .NET assembly

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the script option object

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so=new-object microsoft.sqlserver.management.smo.scriptingoptions

# Step 3 - Check the properties and methods avialable to the script option object

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so | gm

# Step 4 - Now we can set some properties such as IncludeDatabaseContext or NoFileGroup and pass that to the script() method.

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.IncludeDatabaseContext=1

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.NoFileGroup=1

# The script below works fine in SQL Server 2008/R2, however, the script{$so) causes an error in SQL Server 2012 as below:

# script : Cannot find an overload for "Script" and the argument count: "1".

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> gci | % {$_.Script($so)}

# If we want the results from multiple cmdlets concatenated, we use the append parameter.

PS SQLSERVER:\sql\xu_xps\R2\databases\adventureworks2008r2\tables> gci | % {$_.Script($so)} | Out-File c:\ScriptingTables.txt -append

<################################################## Tricks ###################################################>

# Use -noexit to make the display window stay if the result screen disappears very quick (e.g., run powershell c:\service.ps1 in Run)

powershell -noexit c:\service.ps1

<############################################ How to find a job owner and change it #############################################>

# find the jobs owned by the login 'sa'

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |ft name,OwnerLoginName -autosize

# change the job owner to xu_xps\charlie

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |% {$_.Set_OwnerLoginName("xu_xps\charlie")}

# Verify the jobs have new owners

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |ft name,OwnerLoginName -autosize

#The change is only in the current session. You have to call two additional methods to make the change permenent on the server

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | % {$_.alter()}

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | % {$_.refresh()}

# After that, you can verify that in SQL Server Agent

# We can do them all in one line

PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |% {$_.Set_OwnerLoginName("xu_xps\charlie");$_.alter();$_.refresh()}

<###################################################### How to Grant and Revoke Schema Permissions ######################################>

# Step 1 - We need to load the .NET assembly

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the object permssion

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s=new-object microsoft.sqlserver.management.smo.ObjectPermissionSet

# Step 3 - Check the properties and methods avialable to the bject

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s | gm

# Step 4 - Now we can set some properties for the permission object. Let's we want to grand Select, Execute, and View Definition to xu_xps\charlie for all the schemmas

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=1

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=1

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.ViewDefinition=1

# The script below works fine in SQL Server 2008/R2, however, the script{$s) causes an error in SQL Server 2012

# xu_xps\charlie should now have select, execute, and view defintion permissions on all of the schemas in the current PS session and the database

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

# Step 5 - Now let's set Select and Execute to false (leaving ViewDefinition to true)

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=0

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=0

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

<# You thought the Select and Execute permssions for Charlie on all of the schemas are changed to false.

Unfortunately they don't. All of the schmas still have Select, Execute, and View Definition permission.

The changes are only effective in the PS session, they do not affect the values in the database even with the following command:

gci | % {$_.Grant($s,"xu_xps\charlie");$_.Alter();S_.Refresh()}. But when you execute the revoke command, it flips the values

for Select, Execute, and View Definition in the current PS session and that will hit the database, making the permenent changes.

As the current value for Select, Execute, and View Definition is False, False, and True. The revoke command will make them True, True, and False in the database.

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.revoke($s,"xu_xps\charlie");$_.alter();$_.refresh()}