[WMI] permissions for Win32_PerfFormattedData_*

Note: if you're trying to use WMI to query any of the performance counters, you don't just need the normal WMI permissions; you also need the account running the query to be added to "Performance Monitor Users" (or, if using a GPO, "BUILTIN\Performance Monitor Users").  In my case, when I ran the query I'd get nothing.  No errors, no data, nothing.  Adding that group (in addition to all the others like WMI perms) fixed it.

in this case, found it (and more) at: http://vniklas.djungeln.se/2012/08/22/set-up-non-admin-account-to-access-wmi-and-performance-data-remotely-with-powershell/

[Powershell] Credentials/Credential/Credentialing

Had to dig through email to find this, so obviously it needs to go into my aux brain - this blog.

I had a special account set up with WMI permissions.  But that's not the same as the SQL Server account running the script..

• To get credentialing working the first time:
• Log onto the box as the service account for SQL Server
• Run this in Powershell: read-host -assecurestring | convertfrom-securestring |out-file c:\scripts\securestring_theADaccountName.txt # it will ask for the password – provide it.
• (that will read the password then save it, encrypted and readable only by that logged-in user, to the named file.  Name it so that you know which account you have to log in as in order for it to work.)
• To use from then on:

$username = "mydomain\myWMIacct"

$password = cat c:\scripts\securestring_theADaccountName.txt | convertto-securestring

$cred = new-object -typename System.Management.Automation.PSCredential `

         -argumentlist $username, $password

Get-WmiObject -ComputerName myservername -Class Win32_operatingsystem -Namespace "root\cimv2" -Credential $Cred

 Now, when you run the job, it'll run the query as that account.

And if you're feeling like trouble, here's a HORRIBLE alternative:

$credential = New-Object System.Management.Automation.PsCredential("yourdomain\youruser", (ConvertTo-SecureString "yourpassword" -AsPlainText -Force))

[IMOLTP] Detaching/Attaching from one server to another.

So, I was trying to get an in-memory database moved from one server to another (long story, involving IMOLTP melting down on me and resulting in a 2 terabyte log file).

I thought it'd be simple: copy the files over, along with the Filestream folders, and attach.  Nope!  Various errors, including "5(Access is denied.)".

Thanks to Simon


Test:

Create DATABASE demo
go
ALTER DATABASE demo ADD FILEGROUP demo_mod CONTAINS MEMORY_OPTIMIZED_DATA
go
ALTER DATABASE demo ADD FILE (name='demo_mod1', filename='M:\temp\demo_mod1') TO FILEGROUP demo_mod
go
Use Demo

go

  CREATE TABLE dbo.ShoppingCart (   

    ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  

    UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),   

    CreatedDate DATETIME2 NOT NULL,   

    TotalPrice MONEY  

    ) WITH (MEMORY_OPTIMIZED=ON)   

  GO  

Now take offline, copy the Data file, Log file, and the in-memory folder (demo_mod1).

On the target server, right-click, go to security, and add the SQL Server service user and grant FULL CONTROL.

Now, issue this to mount it.  I just tested, and the commented out line doesn't APPEAR to be needed, but that's because my new location matched; if all the locations match, it will grab the demo_mod1 location, probably from the MDF. So in theory, if all your drive letters match, you only need the two FILENAMES. 

CREATE DATABASE [demo] ON 

( FILENAME = N'M:\Data\demo.mdf' ),

( FILENAME = N'L:\Logs\demo_log.ldf' ),

--FILEGROUP [demo_mod] CONTAINS FILESTREAM DEFAULT

(NAME = N'demo_mod1',FILENAME='M:\Data\demo_mod1') -- this is actually a folder...

FOR ATTACH