On the Journey to be a SQL Server Professional: 2/1/15

	Member name	Description
	ContinuePending	The service continue is pending. This corresponds to the Win32 SERVICE_CONTINUE_PENDING constant, which is defined as 0x00000005.
	Paused	The service is paused. This corresponds to the Win32 SERVICE_PAUSED constant, which is defined as 0x00000007.
	PausePending	The service pause is pending. This corresponds to the Win32 SERVICE_PAUSE_PENDING constant, which is defined as 0x00000006.
	Running	The service is running. This corresponds to the Win32 SERVICE_RUNNING constant, which is defined as 0x00000004.
	StartPending	The service is starting. This corresponds to the Win32 SERVICE_START_PENDING constant, which is defined as 0x00000002.
	Stopped	The service is not running. This corresponds to the Win32 SERVICE_STOPPED constant, which is defined as 0x00000001.
	StopPending	The service is stopping. This corresponds to the Win32 SERVICE_STOP_PENDING constant, which is defined as 0x00000003.

<############################################# 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()}