ShellRunas

ShellRunas - http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

Discovered a great tool that is very helpful for testing security. It lets you open any program under a different user account just like "Run as... " in Windows.

Ex: I want to test if my security works correctly in Excel for users in specific roles - I would copy my Shellrunas.exe into C:\Program Files (x86)\Microsoft Office\Office12

Then go to command prompt and run

cd C:\Program Files (x86)\Microsoft Office\Office12
C:\Program Files (x86)\Microsoft Office\Office12>shellrunas excel.exe

This tool has helped me a lot, because the native Windows "Run as..." functionality that could be achieved by holding Shift down and then right click didn't work on my Windows Vista for Excel and "Run as..." is not available for SSMS and for IE as well.

This tool helps work around this limitation:


(c) http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

SQL or MDX query trace in SSRS - trick of imagination or a real thing?

I sometimes have ghost memories - I remember some things that had never in fact happened. And I was wondering if my remembering that Reporting Services 2000 had an option in its configuration file that enabled query logging in Reporting Services log files (LogFiles folder) along with the name of the user who executed the statement. I cannot find anything on Google nowadays. It seems that I have lost my ability to correctly state the question to Google to get my answer.

Anyway, I was wondering if that option was ever available in SQL Server Reporting Services 2000?

I tried to see if query logging for each executed report is available in SQL Server 2005, but did not find anything too. There is of course SQL Profiler, but I was looking for something that would put together username, report ID, execution time AND the MDX or SQL query that was executed with the report.

I guess you could do it manually by comparing execution log table in SSRS with SQL Profiler trace data - but if you have a lot concurrent users running the same report - that could become an unpleasant task.

I tried to see if SSRS Web.config options for Trace could help me

<RStrace>

<add name="FileName" value="ReportServer_" />

<add name="FileSizeLimitMb" value="32" />

<add name="KeepFilesForDays" value="14" />

<add name="Prefix" value="tid, time" />

<add name="TraceListeners" value="debugwindow, file" />

<add name="TraceFileMode" value="unique" />

<add name="Components" value="all,RunningJobs:3,SemanticQueryEngine:4,SemanticModelGenerator:2" />

But it seems that query tracing works only for reports created in Report Builder, and not the old style reports done in Visual Studio.

I am still wondering - was query tracing ever available in SSRS?

Taking a look at CROSS APPLY

Great article on CROSS APPLY - using it could make your life easier!

http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

SSRS Subscriptions failure - should we always listen to our DBA's?

During the configuration of one of the SSRS instances I have stumbled upon a problem with SSRS Subscription. Subscriptions were scheduled but were never executed. "Last run ..." stayed unchanged showing that a subscription execution was never even attempted.

I searched everywhere - at the computer clock, at the SSRS log files, and Event logs but could not find the problem. Googling for the answer didn't help too - since there was no error message by which I could narrow down my search...

Looking desperately everywhere, I peeped in the SQL Services Job Activity Monitor and there I noticed that some jobs with ugly uniqueid names were failing. And to my astonishment the jobs that were failing were the SSRS jobs for the subscriptions!

The problem was - SSRS instance did not like the name of the SSRS database, because it started with the digit, ex: 123_ReportServer. When I tried to execute the SSRS job manually I got the error "Incorrect syntax near '123'" - and when I placed [] around the 123_ReportServer - the job successfully worked and subscription was executed. It took a complete reinstall of the SSRS instance with the new name for the SSRS database for the issue to be resolved.

By the way, you cannot just rename the SSRS database and expect your SSRS instance to work with the new database name. When the ReportServer DB name changes - the complete reinstall is required for SSRS instance to work correctly.

Lesson Learned: never name the database starting with the digit or symbol, even if that was a standard enforced by your local DBA team.