SSRS: Action setting To a URL or Report

Over the years I have created a number of SQL Server Reporting Services reports.  I remember when SSRS first came out, I believe somewhere around 2002, I was thinking that this will be a great product to work with.  And indeed, it has turned out to be a great tool when used in the right situation.

From time to time I run a across something I have never done or seen before.  That is the case with a project I am currently working on.  This project is just a series of reports that will make use of the “Go to Report” functionality.  In this case, a set of criteria will be evaluated to determine if a jump to another report or simply do nothing.

The criteria was based on the version of SQL Server and whether or not the connection is to an Azure SQL DB.  The requirement was that if the criteria is met, allow for navigation to another report. On the other hand, if the criteria is not met do nothing.  In addition to doing nothing, another requirement is to not have the cursor change when hovering over the link, in this case it is actually a text box.  While this post is working with a text box on a report, the same process can be applied to text that will be used to jump to another report.

If you look at the bit of code below, you will see the criteria.  You can use the code below to determine the version.

SELECT SERVERPROPERTY(‘ProductMajorversion’) AS ‘ProductMajorVersion’

This will return a number that will represent the major version of SQL Server.  For example, 10 will be returned for SQL Server 2008 and 2008 r2.  While running on the same code on a SQL Server 2019, you get 15 returned.

The requirement is if the SQL Server is 2012 or newer AND the ProductMajorVersion is not equal to 5, disable the link.  Product Major Version of 5 means an Azure SQL Database.  Any other number will represent a different version of SQL Server.

Below is the expression that I used.  In this case if the version is greater than 10, 2012 or newer AND the Engine Edition of not equal to 5.  The report will navigate to another report.  The report in the block of code is MainReport.

IIF(Fields!ProductMajorVersion.Value > 10 and Fields!EngineEdition.Value <> 5, “MainReport”, Nothing)

Just a reminder.  The IIF function in SQL Server Reporting Services can be used to make a change based on some type of criteria.  This function accepts three parameters.  The first being the expression to be evaluated.  If it is evaluated to True, then the report will navigate to the “MainReport”.  When you float your cursor over the link, the arrow will turn into a hand showing that there is a link. The third argument is what will be used if the expression is evaluated to False.

Using the above example, if major version is 14 and the Engine Edition is 4, then the link to allow the user to navigate to the “MainReport”.  If both criteria are not met, it will use the keyword NOTHING.

It is this keyword NOTHING that will cause the Go To action to do nothing.  The cursor won’t change and when clicked, nothing will happen.

Thanks for visiting my blog!!!



Auditors are at your desk, Now what? – Sysadmin

This is the third in a series of how to help the DBA survive an audit.  The post will focus on how to get the information about who has Administrative rights on the server.

The first two can be found here.

Job Failures

Database Backups

Having worked for both a casino and a publicly traded company, audits have been a big part of my job for quite a few years.  These audits come in many different shapes and sizes, however there are some things that they all have in common, the auditors want evidence!!!  And lots of it!

Surviving an audit and passing an audit are two distinctly different things.  While this blog post is more about surviving, many of these same tools can also be used to help you pass an audit. To me surviving an audit is more about keeping your sanity and being as relaxed as possible.

Please keep this in mind as your read this post.  This is solely based on my experience of working with auditors.  You may have different experiences that might be a bit outside what I am going to talk about here.

Before getting into these useful tools, I want to cover a few basics that I have followed for a number of years.

Get auditors out ASAP

The longer the auditors are in your environment the more opportunity there is for them to find something.  While we all work very hard to do things the right way, we are human and we do make mistakes.  We just don’t want the auditors to find our mistakes. You might be wondering, how can to accomplish this?   The best way to do this is to have the evidence readily available.

Don’t volunteer information

Only provide the evidence they are asking for, nothing more.  You want to avoid giving them things that they do not ask for.  By doing so, you might expose something that you don’t want them to see.  The last thing you want to do, is give them a reason for documenting a finding. Another thing to remember is the audits have a defined scope.

Be honest

Don’t attempt to hide something from the auditors.  They will find what ever the issue is eventually.  Most importantly, we don’t want them thinking we are hiding something from them.  If the auditor doesn’t trust your work it is NEVER a good thing.

Auditors don’t want to hear what you are going to do, they want to see what you have done

If you find something that you feel might be an issue during an audit, start the steps to rectify the situation.  Sometimes if you can show the auditors that you have already taken steps to address the issue, they may be more understanding and things might work out a bit better for you and your company.

Do the right thing every day.

Sadly, doing this might make your job a bit more difficult on a daily basis, but it will make audits much easier to get through.  When the auditors walk in the door, it is too late.  Any thing that might need to be fixed will more than likely not be able to be addressed before they find the issue.  However, if we do the right thing daily, there won’t be much for them to find.

Address Issues in a timely manner

Things fail, that is a reality that we must except. Many times the audits I have been part of, the auditors not only look for documentation that the issue was addressed, but also the it is addressed in a timely manner.  It is important to document what you do, including timestamps.

Remember they are just doing their jobs

Just like you, auditors are held accountable by their management. While audits can be challenging, it is important to remember that the auditors are just simply doing their jobs.

Over the years auditors have asked for lots of information. Sometimes this information is very easy to get, sometimes it might be a bit more challenging.  What I have found that works for me is to have a process already defined and the code ready to go.

So what types of things have auditors asked for?  Below are a few of the common items I have been asked to present to the auditors over the years..  Of course there are a few more items, but these are the main ones that seem to be part of most of the audits I have participated in.  I will go over each of these a bit more in depth in this post and in future posts.

      • Key SQL Server job failures
      • Backup history
      • Orphaned Logins
      • Orphaned database users
      • Who has sysadmin rights
      • Who has db_owner, DDLAdmin and DML rights on key databases
      • Separation of duties

There are a number of tools that you can utilize to capture the required information. However, the focus of this series of blog posts is to utilize native SQL Server functionality.  Below are some of the tools that are available in the SQL Server environment.

      • SQL Server Management Studio
      • Azure Data Studio
      • Azure Data Studio Notebooks
      • Transact SQL
      • Data Management Views
      • SQL Server Audit

Elevated Server Permissions

This is a topic that has come up many times in audits.  These requests usually are about who has server administrative rights and who has the equivalent of db_owner rights for each of the databases.  Usually what we have offered to the auditors has been the list of members of the sysadmin server role and the db_owner database role.

There are a few ways to get this information.  The first maybe the easiest.  That is to use screenshots.

To do this we will need to use SQL Server Management Studio.  Open Object Explorer and connect to the server.  Once you do, navigate through the tree, Security – Server Roles.  It is here that you will find the server roles, including the one we are looking for, sysadmin.

If you right click on the role and go to properties, you will see a screen similar to below.

Notice in the screenshot the date and time in the lower right hand corner are included.  Auditors usually require that the time stamp of when the screenshot was taken be included.

Another way to capture the requested information is to use T-SQL. To do this we will query a few system tables, sys.server_role_members and sys.server_principals.

, ISNULL(, ‘No Members’) AS ‘MemberName’
FROM sys.server_role_members r
RIGHT OUTER JOIN sys.server_principals AS p
ON r.role_principal_id = p.principal_id
LEFT OUTER JOIN sys.server_principals AS s
ON r.member_principal_id = s.principal_id

There is only one item in the WHERE clause, rolename.  In this case we are looking for the SysAdmin role. Just like the screenshot mentioned earlier in this post, the timestamp should be included.  In this case I have included three things.

      • The query
      • The results
      • The row counts

The context in which audits might ask questions about this revolves around “who can make changes to objects” and “who can make changes to jobs”.

Another thing to think about is the scroll bars.  If you look at the image below, you will see the vertical scroll bar from SSMS.  Notice in the red box there is room to scroll down. Several times over the years I have had auditor ask what is lower.  So ideally if you can design the query so that a scroll bar is not needed, that would make things much easier.

You really have nothing to fear in audits as long as you are prepared and do the right thing daily.  As mentioned earlier, this is the third post in the series.

Thanks for visiting my blog!!!