Giving Permissions to BUILTIN\Users in SQL Server

I had a script for granting access to a SQL Server Express database to all users that looked like this:

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BUILTIN\Users')
BEGIN
	CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users]
	EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
	EXEC sp_addrolemember N'db_datawriter', N'BUILTIN\Users'
	GRANT EXECUTE TO [BUILTIN\Users]
END

The problem was that this only worked on English Windows. For example, on a Swedish Windows, this group is called “BUILTIN\Användare”. When looking at the sys.server_principals system view, I noticed that the SID is constant (0x01020000000000052000000021020000) but the name varies, so I could solve this with a dynamic SQL script:

DECLARE @builtinUsers nvarchar(128), @sqlString nvarchar(max)
SET @builtinUsers = (SELECT name FROM sys.server_principals where sid=0x01020000000000052000000021020000)
SET @sqlString = 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @builtinUsers + ''') CREATE USER [' + @builtinUsers + '] FOR LOGIN [' + @builtinUsers + ']'
EXEC sp_executesql @sqlString
SET @sqlString = 'EXEC sp_addrolemember ''db_datareader'', ''' + @builtinUsers + ''''
EXEC sp_executesql @sqlString
SET @sqlString = 'EXEC sp_addrolemember ''db_datawriter'', ''' + @builtinUsers + ''''
EXEC sp_executesql @sqlString
SET @sqlString = 'GRANT EXECUTE TO [' + @builtinUsers + ']'
EXEC sp_executesql @sqlString
GO
Posted in SQL Server | Tagged , | Leave a comment

ReportViewer control in MVC

Continuing from my previous post, I wanted to make my reports available in my ASP.NET MVC site. I read about the ReportViewer control that it works only on webformst (ASPX pages), due to viewstate requirements. Luckily, webforms ges can be added to an MVC project. But when I started passing values to the report parameters, the page started some kind of inifite loop. That was because I had forgotten about webforms development and that you must often have a check for postback in code-behind. Like this:

    public partial class ReportWebForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ReportViewer1.ServerReport.ReportServerUrl = new Uri(Properties.Settings.Default.ReportServerUrl);
                var userUnit = Session[LoginModel.EnhetSessionKey] as Enhet;
                if (userUnit == null)
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", "[Organisation].[Organisation-Enhet].[All]"));
                }
                else
                {
                    ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter("Unit", string.Format("[Organisation].[Organisation-Enhet].[Enhet].&[{0}]", userUnit.Id)));
                }
            }
        }
    }
Posted in Uncategorized | Leave a comment

Date Ranges in SSRS and SSAS

I have taken my first steps in Reporting Services (SSRS) land. The reports use data from a multidimensional cube in Analysis Services (SSAS). Of course, one requirement was to filter the reports on a date range. Should be simple, right? Well, at least not for me.

When designing the reports, I created a new dataset, used the MDX query designer and added my date dimension as a filter. I choose Range (Inclusive) and checked the two parameter checkboxes. Visual Studio automatically added these parameters both to the dataset and the report.

The problem was that the key of my dimension was a date column, but the tool added text parameters and choose available values from a query. That wasn’t what I wanted. I wanted a calendar drop down. So I changed the parameters to date and as available values I choose None. OK, now I had the calendar. But the report didn’t work. I got “The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated”.

It turned out I had to use a completely different expression for the dataset parameter expression. The expression should return a string that contains a dimension filter. I tried:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-dd") + "]"

(My dimension is called “Datum” and so is my key attribute.)

It didn’t work. I got an error saying 2014-05-01 couldn’t be converted to the type date. Now I thought that SSAS was using US formatted dates internally, so I tried “MM-dd-yyyy”. Didn’t work either. After some Bing research I finally managed to find the solution: include time in the string as well:

="[Datum].[Datum].&[" + Format((Parameters!StartDate.Value), "yyyy-MM-ddTHH:mm:ss") + "]"

By the way, here is how to set the default for the report parameters to start and end of previous month:

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Now()), Month(Now()), 1))
=DateAdd(DateInterval.Day, -1, DateSerial(Year(Now()), Month(Now()), 1))

Posted in SQL Server | Tagged , | Leave a comment

Microsoft Azure Automation

Microsoft recently added a very useful feature to automate things in Azure. It is in preview at the time of writing, but I decided to try it for starting and stopping our virtual machines used for testing, and it works really well. Here is a link to a step-to-step guide: http://blogs.technet.com/b/keithmayer/archive/2014/04/06/step-by-step-getting-started-with-windows-azure-automation.aspx

Here is my “workflow” for starting:

workflow Start-nnn
{
    # Specify Azure Subscription Name
    $subName = 'nnn'
    # Connect to Azure Subscription
    Connect-Azure -AzureConnectionName $subName
    Select-AzureSubscription -SubscriptionName $subName

    # Start VMs
    $vmList = ('vm1','vm2')
    For ( $vmCount = 0; $vmCount -lt $vmList.Count; $vmCount++) {
        Write-Output ("Getting virtual machine status for {0}..." -f $vmList[$vmCount])
        $vm = Get-AzureVM -ServiceName $vmList[$vmCount] -Name $vmList[$vmCount]
        if ( $vm.InstanceStatus -eq 'StoppedDeallocated' ) {
            Write-Output ("Starting {0}..." -f $vm.Name)
            Start-AzureVM -ServiceName $vm.ServiceName -Name $vm.Name
        }
    }
}

And for stopping:

workflow Stop-nnn
{
    # Specify Azure Subscription Name
    $subName = 'nnn'
    # Connect to Azure Subscription
    Connect-Azure -AzureConnectionName $subName
    Select-AzureSubscription -SubscriptionName $subName

    # Shutdown VMs
    $vmList = ('vm1','vm2')
    For ( $vmCount = 0; $vmCount -lt $vmList.Count; $vmCount++) {
        Write-Output ("Getting status for {0}..." -f $vmList[$vmCount])
        $vm = Get-AzureVM -ServiceName $vmList[$vmCount] -Name $vmList[$vmCount]
        if ( $vm.InstanceStatus -eq 'ReadyRole' ) {
            Write-Output ("Stopping {0}..." -f $vm.Name)
            Stop-AzureVM -ServiceName $vm.ServiceName -Name $vm.Name -Force
        }
    }
}
Posted in Azure | Tagged , | Leave a comment

Filter Diagnostic Trace Using Configuration

I often have situations when I want to have different levels of diagnostic trace in different environments, and want to configure this in web.config or app.config. Here is an example of how to do it. This example traces to the eventlog with information, warning and error levels.

  <system.diagnostics>
    <trace autoflush="true" indentsize="0">
      <listeners>
        <add initializeData="MyEventSource" type="System.Diagnostics.EventLogTraceListener" name="MyEventListener">
          <filter type="System.Diagnostics.EventTypeFilter" initializeData="Information" />
        </add>
      </listeners>
    </trace>
  </system.diagnostics>
Posted in Uncategorized | Leave a comment

Automatically Shut Down an Azure VM

Here is a self-documenting (I hope) PowerShell script. Windows Azure PowerShell is required.

Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
# Importing publish settings only has to be done once.
# Download from <a href="https://windows.azure.com/download/publishprofile.aspx">https://windows.azure.com/download/publishprofile.aspx</a>
# Then, run the following line:
# Import-AzurePublishSettingsFile "Path\File.publishsettings"
# Test:
Get-AzureVM -ServiceName "MyService" > C:\Users\user\Documents\StopLog.txt 2>&1
# Stop the VM:
Stop-AzureVM -Name "MyName" -ServiceName "MyService" -Force >> C:\Users\user\Documents\StopLog.txt 2>&1
# Schedule a task (using Task Scheduler) with
# Action: Start a program
# Program/script: powershell.exe
# Parameters: C:\Users\user\Documents\StopVM.ps1
Posted in Computers and Internet | Tagged | Leave a comment

Deploying to SQL Server Analysis Services (SSAS) using PowerShell

My previous post was about configuring http access to SSAS. Here is a way to deploy a SSAS project using script (PowerShell) via http.

First, create a project configuration. I called mine “Test”. Then, edit the “.deploymenttargets” file located in the project configuration folder in the bin folder (MyProject\bin\Test in my case). Edit the server parameter to e.g.

https://myserver.cloudapp.net/OLAP/msmdpump.dll

Then, create a deployment script using the deployment utility:

“C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment” MyProject\bin\Test\Model.asdatabase /s:ScriptLog.txt /o:Model.xmla /d

Then, deploy using the new PowerShell commandlets:

$credential = Get-Credential domain\user
Invoke-ASCmd -InputFile $fspath\Model.xmla -Server https://myserver.cloudapp.net/OLAP/msmdpump.dll -Credential $credential

Here is the entire script:

$fspath = (Get-Location).Path
Write-Host "Generating deployment script..."
& "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment" MyProjct\bin\Test\Model.asdatabase /s:ScriptLog.txt /o:Model.xmla /d
Start-Process $fspath\ScriptLog.txt
Write-Host "Importing SQL modules..."
Import-Module SQLPS -disablenamechecking
Import-Module sqlascmdlets
Write-Host "Invoking deployment script... This may take several minutes or even hours."
$credential = Get-Credential domain\user
Invoke-ASCmd -InputFile $fspath\Model.xmla -Server https://myserver.cloudapp.net/OLAP/msmdpump.dll -Credential $credential | Out-File $fspath\Result.xml
Start-Process $fspath\Result.xml
Write-Host "Done."
Posted in Uncategorized | Leave a comment