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')
	EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
	EXEC sp_addrolemember N'db_datawriter', N'BUILTIN\Users'

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

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))