SQLBalls

Wednesday, November 10, 2010

Powershell 2.0 Required for UCP on Windows XP

So I’m trying to install SQL Server 2008 R2 Utility Control Point, so I can take a look at all that the product has to offer and see how we can utilize it at my current place of business.

For the sake of full disclosure I’m doing this on a laptop running Windows XP SP 3, x86, Dual Core Processors and about 3 GB of memory.

I installed it about a week ago, and set up another named instance and had a SQL Agent Job running stored procedures from Adventure works every minute, to help simulate a work load on one of the databases.

I’ve read how it can take up to 45 minutes to get data posted to the dash board and so I set the jobs and got busy doing other things. One week later I take a look at my Utility Explorer to see all the beautiful looking screens.

Needless to say this isn’t what I expected to see. It almost looks as if there is no data. So I check my enrolled instances to see what status they are running under.

Now I’m really bothered, apparently something has gone wrong. So I take a look at my SQL Agent jobs to see if the jobs were created and if the history has been running properly.

I see that my sysutility_mi_collect_and_upload job has been failing. So I isolate just that job, seeing as how collecting and uploading the data is directly related to seeing nice dashboards (or so I would guess).

And YOWZA! This has not been running at all, a quick glance back over the history and I can see this job has never run correctly. Let’s take a closer look at why this is failing.

Step 2 of the job is failing and the error is big and ugly The important part is the following:

(Get-Wmiobject <<<< Win32_MountPoint) | InvocationName : Get-Wmiobject PipelineLength : 1 PipelinePosition : 1 WARNING : 11/8/2010 12:25:10 PM : 00000000000000000000000000000000000000000000000000000000000000000000000000000000WARNING : 11/8/2010 12:25:10 PM : ErrorRecord : Command execution stopped because the shell variable "ErrorActi onPreference" is set to Stop: Invalid class StackTrace

When I set up the UCP I remember seeing one Yellow Warning/Yield Sign. Honestly I breezed right by it, because if it’s not going to work you get a big red Error sign, right. Right? Wrong.

The Warning box was pointing to WMI not being set up correctly. There are several Powershell class files that are required by UCP. So the Question is what are they and how do you check that they are there.

They are:

Win32_MountPoint

Win32_Volume

Win32_LogicalDisk

Win32_PerfRawData_PerfProc_Process

Win32_PerfRawData_PerfOS_Processor

Win32_Processor

So how do you check for them? Open up PowerShell and type get-wmiobject [objectname]

As you can see from above this is the error you will get if you are missing one of the classes.

A little more research shows me that Powershell 1.0 and Windows XP does not contain Win32_MountPoint and Win32_Volume classes. This was reported on Microsoft Connect at here: https://connect.microsoft.com/SQLServer/feedback/details/540412/sysutility-mi-collect-and-upload-job-failure?wa=wsignin1.0 .

Microsoft Employee Jennifer Beckmann reported that the user was able to get everything working after installing Powershell 2.0

The link above lists a “work around”, by a user named Dryknot. I would not recommend this solution as it recommended opening up the SQL Agent job and editing the PowerShell Code. No offense to Dryknot I’m sure that she/he is a perfectly great person, and that they took the time to post a work around to help others shows that they are indeed a great person just trying to help others. But there are 2 reasons I would not make this change.

You are changing the requirements that are being gathered from one class, Win32_MountPoint to another, Win32_MappedLogicalDisk. So it’s like saying I want to gather information on an Orange and buying an Apple. One thing is not like the other, even though they are both drive classes.
I’m missing 2 classes. Even if I were to fix the Win32_MountPoint issue, I would still have one with Win32_Volume. And if you use Mount Point’s you know how critical it is to gather space usage on them. DON’T CHEAP OUT HERE. Do not accept substitutes go for the original.

So If you are planning on implementing a UCP on a Windows XP machine make sure that you have Powershell 2.0 running or you could end up with the same results I did.

Thanks,

Brad

******DISCLAIMER********

All advice is AS IS. There are no warranties or services provided as a result of anything you see here.

Tuesday, November 9, 2010

Dynamic Permissions

I needed to script out a database user role and all of the permissions that this role has with it for a server migration. We are moving the databases from on server to a new location. You could try the Generate script tool, but while that would give me the script to re-create the role it did not give me all of the rights that role currently has.

I opened up the advanced option and still not luck.

So I did a quick Google search on how to script out roles and found an old post by SQL MVP Russell Fields where he was answering a question on the SQL Security Forum, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/threads.

Russell’s Response, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/26ffcac4-8d4c-4107-b355-80e63344fb8e listed how to use the DMO’s sys.database_permissions and sys.database_principals in order to query a database to find out the roles that where listed for the database, as well as the permissions each of those roles granted.

So my first step is most of the way done. I now know how to get the info, now I want to dynamically generate a script that will re-create this role for me, and allow me to use this on multiple databases.

One thing I will need to do is add the schema to the return results, as we will want to make sure our object mappings are correct. So I’ll add sysobjects and the schema_name() function to this query to pull that back. My first results look like this.

select

u.name,

p.permission_name,

p.class_desc,

schema_name(o.uid),

object_name(p.major_id) ObjectName,

state_desc

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name='DatabaseRoleName'

order by

ObjectName, name, p.permission_name

You will want to fill in the Database role you are looking for in the Where clause, or remove the Where to get a full listing of all the permissions for all of the roles in the database you are querying.

So now we’ve got everything we want, now I need to string it all together into the syntax I would like.

select

u.name,

p.permission_name,

p.class_desc,

schema_name(o.uid),

object_name(p.major_id) ObjectName,

state_desc,

(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name='DatabaseRoleName'

order by

ObjectName, name, p.permission_name

This gives me exactly what I want syntax wise, but now I need to insert “GO” in between each row, and I would like to make sure and specify the database that I’m working in, so I have a full script generator.

So now I’ll flush out the script a little bit, I need to do some row by row alterations, and I’m not looking at a large set of data, so I’ll use a table variable to hold all the syntax that I’m going to generate.

--======================================================

--Declare our variables

--======================================================

declare @dbname varchar(500), @i int, @x int, @sqlcmd varchar(max), @RoleName nvarchar(500)

--======================================================

--Get the Current DB Name

--======================================================

set @dbname = (select DB_NAME())

set @Rolename = N'DatabaseRoleName'

--======================================================

--Declare the 2 Table Variables I'll be using

--======================================================

declare @myTable as Table(

[myID] [int] IDENTITY(1,1) NOT NULL,

mySQLText varchar(max)

)

declare @myTable2 as Table(

[myID] [int] IDENTITY(1,1) NOT NULL,

mySQLText varchar(max)

)

--======================================================

--Populate @myTable

--======================================================

insert into @myTable

select

(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText

from

sys.database_permissions p

join sys.database_principals u

on p.grantee_principal_id = u.principal_id

join sysobjects o

on p.major_id=o.id

where

u.name=@Rolename

--======================================================

--Set the @i count = to the count from @mytable

--Set @x = 0

--======================================================

set @i = (select count(*) from @myTable)

set @x=0

--======================================================

--Begin the loop to create our dynamic statements

--you can either use table 2 or you could print the values

--to have a text you could just copy and paste

--this will work either way

--======================================================

while @x < @i

Begin

set @x= @x +1

if (@x=1)

Begin

set @sqlcmd= 'Use [' + @dbname + ']'