Changing Permissions for Task Scheduler and Other Services

At work I wanted to restart the Task Scheduler service on a server, but found that I did not have the permission to do so.  As seen in the screenshot below, the buttons Start, Stop, etc. are all greyed out:

After some Google searches, I found a good thread discussing this problem on SuperUser: http://superuser.com/questions/315705/how-to-assign-permissions-to-manage-windows-service-when-uac-is-enabled.  The answer gave a brief but good explanation of the situation as well as a couple of ways to resolve it.

I vaguely remember coming across this problem before. So I thought I should blog it this time for future references.  Below are the steps to fix this problem.

Firstly we need to find the particular process running the Task Scheduler service.  This service, like several other services, is run inside of one of the svchost.exe processes.  Open a Command Prompt as Administrator, then enter the command:

     tasklist /svc /fi "services eq schedule"

The screenshot below shows the process svchost.exe with PID of 828 is running Task Scheduler:

Next, run Process Explorer from Sysinternals.  As you hover the mouse over the process with PID 828, we can confirm that Task Scheduler is indeed running inside this process:

Next, right click on this process and select Properties.  Then go to Services tab:

Highlight Schedule in the list of services then click the Permissions button.  Here you can see, even users in the Administrators group only have Read access:

This is easily remedied by changing the permission to Full Control:

Finally click the OK button to apply the changes.  Next, let's go back to Task Scheduler in Windows services.  Now we can control the service to our heart's content:

And of course we can use the same method to modify other Windows services.

Run MDX Queries Under Specific Roles

I wanted to run some MDX queries under specific roles.  When you browse a cube in SQL Server Management Studio (SSMS), you can change your security context by using the Change User botton (1st one from the left):

Then pick the roles:

But there is no such button when you run MDX queries in SSMS.  So how do you do it?  As it turns out you can change the connection properties and add connection string parameters, as shown below:

Note that in order for this to work you need to be a server admin on SSAS.

Control Locking with SQL Full Text Search and CONTAINSTABLE

One of our systems at work uses SQL Full Text Search to let users search for products using text descriptions. However, users often complain of slow performance or even time-outs.

After some investigation, I concluded that, besides performance issues, there were also some process blocking due to SQL locks. So I not only restructured these queries and added indexes to make them more efficient, I also added "WITH (NOLOCK)" to some of the queries (where dirty reads is not an issue).

My further testing showed that locks on a table will also block Full Text Searches using CONTAINSTABLE(). However, you cannot add "WITH (NOLOCK)" when you are selecting from CONTAINSTABLE(). So, is it possible to ignore locks when using CONTAINSTABLE()?

Well, as it turns out, you can. I remembered that "WITH (NOLOCK)" is equivalent to setting the transaction isolation level to READ UNCOMMITTED. So I just set it before the CONTAINSTABLE() statement and reset it afterward, like so:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
    KEY, RANK
FROM CONTAINSTABLE(Product, Description, @SearchTerm);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Testing confirmed that this allows the use of CONTAINSTABLE() while ignoring table locks. And as they say, voilà, problem solved.

Black Logon Screen on Windows Server 2003

One of our servers, for some reason, has gone all black on the logon screen. It has been like that for a while. You can still login by typing in the login details "blind", but the logon screen is just completely black, whether via Remote Desktop or the console.

Today I finally decided to google for some info about it. Sure enough it's pretty simple to fix. It is caused by the registry for the default user's color scheme (under [HKEY_USERS\.DEFAULT\Control Panel\Colors]) being set to all zeros. And it can be fixed by setting the registry values to some proper values, for example, by exporting your own set of colors and importing them into the Default User.

Here is the Microsoft support article explaining it all: http://support.microsoft.com/kb/906510.

Using SQL Server Aliases

Recently we set up a SharePoint 2010 development environment at work.  I was tasked with adding the SSRS 2008 R2 server to the farm and setting it up to run in integrated mode.

I followed the instruction in MSDN, firstly installing SharePoint then tried to join it to the farm.  However, it kept giving an error when I hit apply on the config database.  I digged around, and found in SharePoint it not using the actual server name of the config database.  Instead, it is using a alias to specify the SQL server host name.  It worked without problems after I create the same alias on the SSRS box and using it as the config database server.

You can create these aliases from SQL Server Configuration Manager.  Or for machines that don't the SSCM installed, you can use "cliconfg.exe", or SQL Server Client Network Utility, instead (see screenshot below).  This utility seems to be shipped with pretty much all current versions of Windows from XP/2003 onwards.

In the past I have been advocating the use of DNS entries instead of actual server names where the server could change.  This has the advantage that if you need to move the database (or whatever function the machine serves) to another server, it's a simple matter of updating the DNS entry.  In addition, this also allows you to provide a nice, easy to remember name if users will be accessing it directly.

Using SQL Server Aliases provides a similar facility that allows you to change the actual server with minimal change.  The difference is that you may need to update the alias in multiple places.  However, it is still a good option if, for some reason, it is not possible or not easy to make changes to the DNS.

Why CurrentMember Can Be Used Only On Hierarchies

I used to wonder why the MDX function CurrentMember applies only to hierarchies and not to levels.  For example, the Members function can be used on either a hierarchy or a level, and returns the appropriate set of members.  I thought it would make sense if CurrentMember worked in a similar way.

Today while reading Chapter 10 - MDX Concepts of the book Microsoft SQL Server 2008 Analysis Services Unleashed, it suddenly hit me.  I realised why CurrentMember should only be applied to hierarchies and not to levels.

The book was explaining the concept of Execution Context and how SSAS works out the current coordinates for a query.  The SSAS engine starts by setting the current coordinates to the default member of each attribute hierarchy.  In most cases this would be the All member.  Then it takes the members that have been specified in the query and overwrites the appropriate attributes.  However, the coordinates for those attributes not specified in the query will remain at their default member.  Therefore in most cases, these attributes' CurrentMember is still the All member.


So that's why the CurrentMember function can be applied only to hierarchies and not to a specific level: because it needs to be able to return any member from the whole hierarchy, including the All member.  Applying the function to a specific level could return invalid results.


Rethinking about this, it all seems be a bit obvious now, but I guess it never clicked for me before.  So I am happy to share it in case you have wondered about the same thing.

Windows 7's Boot from VHD - Transferring the VHD to a New Hard Drive

You might be aware that Windows 7 and Windows Server 2008 R2 are capable of booting from a VHD.  The advantages of this set up include ease of backing up the whole O/S partition and the ability to dual-boot (or multi-boot) without the need to create multiple partitions.

If you search for terms such as "Windows 7 boot VHD", you will get lots of hits on how to install a new instance of the O/S using this feature.  But what if you have already set up such a booting arrangement, and now wants to move the VHD from one drive to another?  For example if you have bought a new faster hard drive and want to transfer over the VHD.  I haven't found any post describing how to do this.

One way to do this would be to run through the O/S install process again on the new hard drive, creating a brand new VHD boot.  Then just copy your existing VHD onto the new hard drive, overwriting the new VHD you just created.  However, this meant going through the whole install process again, and I wanted to find if there is a faster way to do this.  After some experiments and some searches on the web, the following steps worked for me (assuming you have connected up the new hard drive, created a primary partition and formatted it):

1. Boot up your machine in Windows 7 install DVD.
2. Press shift-F10 to bring up the command prompt.
3. Copy the VHD to the new hard drive.  (You could do this beforehand if you have another O/S you could boot up in, so that the VHD could be copied.)
4. Power down and disconnect your old hard drive.  This step is optional, but it avoid any confusion between new and old drives.  Then repeat steps 1 and 2 to get back to the command prompt.
5. Run diskpart, then enter these commands (where C:\Win7.vhd is the location of your VHD on the new hard drive):
• select vdisk file=C:\Win7.vhd
• attach vdisk
• exit
6. From the command prompt, enter:
• bootsect /nt60 C: /mbr
7. From the command prompt, enter (where E: is the drive letter of the attached VHD):
• bcdboot E:\Windows /s C:
8. Exist the command prompt
9. Go back and follow the main Windows 7 install screens.  On the 2nd screen, click the "Repair your computer" link.
10. On System Recovery Options screen, choose "Use recovery tools" then click Next.
11. Choose the "Startup Repair" option.
12. After it has done its work, click the Finish button.  It will reboot.

Now the machine should boot up in your original VHD, running on your new hard drive.

Some additional notes:

• Instead of pressing shift-F10 (or if you don't remember which keys to press), you can also go through the "Repair your computer" option (steps 9-10) to get to the option to bring up the command prompt.
• These steps assumes you have formatted your new hard drive while running in Windows.  That would be easier and safer.  If you consider yourself hardcore, you can do the formatting from within diskpart in step 5.  But be very careful, because if you select the wrong disk, you would wipe out your existing hard drive.
• If you are running on a physical partition, you can convert it to booting from VHD.  Firstly, use Disk2vhd to copy your existing partition to a VHD.  Then follow the steps similar to above to set up booting from the VHD.