Saturday, July 18, 2009

Enable Snapshots

Very useful to avoid lockouts with DBs being accessed and updated...:


ALTER DATABASE YourDatabase
SET SINGLE_USER WITH NOWAIT
GO
ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE YourDatabase
SET MULTI_USER WITH NOWAIT
GO

--Report status on all databases
SELECT name,
CASE is_read_committed_snapshot_on
WHEN 1 THEN 'yes' ELSE 'no' END AS Read_Committed_Snapshot_Enabled
FROM sys.databases

Thursday, July 16, 2009

Landshare USA?

I'm a big fan of the River Cottage series in the UK particularly Hugh's most recent addition, the Landshare program where people who want to grow their own food but don't have the space are put in contact with those that do and vice versa... brilliant! Anyone interested in starting something similar in the U.S.A.?

RSOP through WMI

I've noticed a lot of people with problems retrieving user Resultant Set Of Policy - RSOP information from WMI using root/RSOP... computer RSOP works fine:

strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\rsop\computer")
Set colItems = objWMIService.ExecQuery("Select * from RSOP_GPO")
For Each objItem In colItems
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "GUID Name: " & objItem.GUIDNameWscript.Echo "ID: " & objItem.ID
Wscript.Echo "Access Denied: " & objItem.AccessDenied
Wscript.Echo "Enabled: " & objItem.Enabled
Wscript.Echo "File System path: " & objItem.FileSystemPath
Wscript.Echo "Filter Allowed: " & objItem.FilterAllowed
Wscript.Echo "Filter ID: " & objItem.FilterId
Wscript.Echo "Version: " & objItem.Version
Wscript.Echo
Next

Works without issue but root\rsop\user\%SID% does not retrieve anything... to fix you must replace the hyphens - in the SID with underscores _ !!

So:
S-1-5-21-1708537768-688789844-1994488-12833 should be S_1_5_21_1708537768_688789844_1994488_12833

so:

strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objAccount = objWMIService.Get("Win32_UserAccount.Name='joeblow',Domain='YourDomain'")
Wscript.Echo objAccount.SID
strWMISID = replace (objAccount.SID,"-","_")
Set objWMIService = Nothing
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\rsop\user\" & strWMISID)
Set colItems = objWMIService.ExecQuery("Select * from RSOP_GPO")
For Each objItem In colItems
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "GUID Name: " & objItem.GUIDNameWscript.Echo "ID: " & objItem.ID
Wscript.Echo "Access Denied: " & objItem.AccessDenied
Wscript.Echo "Enabled: " & objItem.Enabled
Wscript.Echo "File System path: " & objItem.FileSystemPath
Wscript.Echo "Filter Allowed: " & objItem.FilterAllowed
Wscript.Echo "Filter ID: " & objItem.FilterId
Wscript.Echo "Version: " & objItem.Version
Wscript.Echo
Next

Voila! it now works...

FYI - Get the logged on user with:

Set colSessions = objWMI.ExecQuery("Select * from Win32_LogonSession Where LogonType = 2")
If colSessions.Count = 0 Then
Wscript.Echo "No interactive users found"
Else
For Each objSession in colSessions
Set colList = objWMI.ExecQuery("Associators of {Win32_LogonSession.LogonId=" & objSession.LogonId & "} Where AssocClass=Win32_LoggedOnUser Role=Dependent" )
For Each objItem in colList
WScript.Echo "Username: " & objItem.Name & " SID: " & objItem.SID
WScript.Echo "WMISID: " & replace(objItem.SID,"-","_")
Next
Next
End If

Consistency is a wonderful thing... maybe the WMI namespace adheres to some standard that prevents hyphens but allows underscores... who knows...

Friday, July 10, 2009

Old junk SSMS (SQL Server Management Studio) Crashes opening query with CROSS APPLY

How damn annoying SQL 2005 SP3 Management Studio crashes everytime when trying to open an existing query in the designer that has a CROSS APPLY in it! arggh!

No worries it's fixed in SQL 2008 Management Studio, well the error is trapped and it just says it can't display the query rather than crashing but it's better than nothing...

Search Brian Hehir's sites

Loading