Determining the Windows Groups for a SQL Server Login

There was a question this morning on the SQL Down Under mailing list about how to determine the Windows groups for a given login.

That’s actually easy for a sysadmin login, as they have IMPERSONATE permission for other logins/users.

Here is an example procedure:



When I execute it on my system this way:


It returns the following:


Note that the Usage column could also return “DENY ONLY” or “AUTHENTICATOR”.

SQL Down Under Demographics and Technologies

As most websites do, we collect analytics on the people visiting our site

I thought it might be interesting to share the breakdown of visitors to our site. Keep in mind that we have a primarily Microsoft-oriented audience. Enjoy!

No surprise on the native languages:


Country breakdown reflects the amount of local traffic we have for instructor-led courses. Most others are podcast listeners:


We first noticed Chrome slightly outstripping IE a while back but recently, it’s changed a lot. I suspect that IE11 will have been as issue here:


No surprises on the operating systems but Linux continues to disappear from our clients. It used to be higher:


The big change has been in mobile operating systems. It’s the first time that iOS has only managed 50%. It used to be 82% for us:


We’re also seeing a shift in screen resolutions:


And this is the mix of where our site visitors come from:


SQL Down Under Show 64 – Ryan Crawcour–What SQL Server DBAs and Developers Need to Know About Azure DocumentDB

I had the pleasure of recording another SQL Down Under show today.

Show 64 features Microsoft Azure DocumentDB product group member discussing Azure DocumentDB and what SQL Server DBAs and developers need to know about it.

JSON-based storage has been one of the highest rated requests for enhancements to SQL Server. While we haven’t got those enhancements yet, DocumentDB nicely fills a gap between NoSQL databases (I use the term loosely Smile ) and relational databases.

You’ll find the show here:


Partner events for SQL Server 2014 and Power BI

Over the last year, I’ve delivered a number of partner enablement events for Microsoft. These events are low cost training sessions that run for three days. Days 1 and 2 cover SQL Server 2014 content, mostly regarding in-memory OLTP, clustered columnstore indexes, and Azure integration with hybrid systems. Day 3 covers the full Power BI stack.

We’re pleased to be running another set of these around the country:

Melbourne: November 5th to 7th

Perth: November 24th to 26th

Sydney: December 8th to 10th

I’d love to see many of you there. I’m looking forward to delivering them. To find out more, follow these links:



SSMS SQL Server 2014 CU4 Issue: The type initializer for ‘PropertyMetadataProvider’ threw an exception


When I installed CU4 for SQL Server 2014, I started to receive an error in SSMS (SQL Server Management Studio) every time I connected to a database server:


It was interesting that it was a type that wasn’t being found in Microsoft.SqlServer.SqlEnum. I presumed it was a problem with a type being missing in that DLL and that I must have had an older one.

Turns out that the problem was with the Microsoft.SqlServer.Smo.dll.

The product team told me that “bad” DLL versions were pushed out by the first released version of SSDT-BI for VS2013. All was working fine though until I applied CU4; then the errors started.

While the correct file version was 12.0.2430.0, and that was the one I had in the folder, the issue seems to actually relate to the Microsoft.SqlServer.Smo.dll, not to the SqlEnum dll. For some reason the installer didn’t correctly replace the previous entry in the GAC. It was still a 12.0.2299.1 version.

What I ended up having to do was to use ProcessExplorer to locate the dependencies of the ssms.exe process, then find the version of Microsoft.SqlServer.Smo.dll that was being loaded. I renamed it to stop it being loaded and rebooted. Then I found I still had the error and there was another location that it loaded it from. Again I renamed it and rebooted. Finally the error said that it couldn’t find the file at all.

At this point, I did a Repair of the Shared Features on “SQL Server (x64)” from “Programs and Features”, then deinstalled CU4, rebooted, then reinstalled CU4.

And now it seems to work ok. (Although it did nuke the previous entries from the connection dialog in SSMS)

Hope that helps someone.

FIX: Visible preview window in SQL Server Reporting Services gone!

It’s great to see that the Connect site leading to fixes in the product.

I was really pleased when SQL Server Data Tools for BI appeared for Visual Studio 2013. What I wasn’t pleased about where a number of UI issues that came with that version.

In particular, there was a problem with previewing Reporting Services reports. If I create a new report project, add a blank report, and drag on a text box:


Note that when I clicked the Preview button, the following appeared:


It appears that the preview is provided by a program called PreviewProcessingService.exe that I presume was meant to be launched on-demand in the background. If you closed the window, an error appeared in your preview. If you minimized it, you could happily ignore if from that point on.

I reported it in the Connect site, and am happy to see today that a new KB article appeared with a fix for it.


What the KB 2986460 article provides is a link to a new downloadable version of SSDT-BI for VS2013:


When the article first appeared, I downloaded the version immediately. It did not fix the problem. Unfortunately, the KB article appeared one week before the download was updated. If you downloaded it before and it did not fix the problem, unfortunately you will need to download it again. Here are the file properties of the version with the fix:


Be forewarned that the fix is a complete replacement that is 1GB in size:


It would be great if they can get to the point of patching these programs without the need for complete downloads but I’m very pleased to see it appear regardless.

Easier way to try SQL Server Availability Groups using Azure

Over the last year, I’ve been delivering a number of partner enablement training sessions for Microsoft. They target the changes in SQL Server 2014 and also provide an introduction to Power BI.

During the sessions for day two, I have been getting the attendees to set up a high availability environment in Azure, with a Windows Server 2012 R2 domain controller and a couple of SQL Server 2014 Enterprise member servers. This process basically involves:

  • Create the virtual network
  • Create the cloud service
  • Create and configure the domain controller VM and a fileshare
  • Create and configure the SQL Server 2014 VMs
  • Create a windows cluster for the three machines
  • Configure SQL Server Availability Groups
  • Configure an availability group listener that connects via the cloud service

That spread over three labs that took about two hours in total. It’s a good learning exercise because it gets you to understand every step along the way.

However, if you just want this type of setup created very quickly, there’s now a much easier way to do it within minutes. The SQL Server team have created a template that already includes all these options. When I first went looking for it, I couldn’t find it in the templates list:


I was still using the old portal. I needed to look in the new portal. You can get from the old portal to the new one by this link in the top right-hand corner. You need to click the little person icon, then choose from the menu:



In the new portal however, it’s easy to find:


I’d encourage you to try it to see how it simplifies the process.

By the way, if you are a Microsoft Partner and want to attend one of the upcoming sessions (in Melbourne, Perth, and Sydney), ping your partner contacts and find out if you can attend. I’d love to see you there.