Archive for August, 2015

Run a SSRS subscription manually

August 27, 2015

Use the following query to locate the report on which your subscription is based and execute the string in the execute_subscription field.

SELECT a.[Path] ,
a.Name ,
CONCAT(‘exec [ReportServer].dbo.AddEvent @EventType=”TimedSubscription”, @EventData=”’,b.SubscriptionID,””) execute_subscription ,
c.ExtensionSettings
FROM ReportServer.dbo.Catalog a
INNER JOIN ReportSchedule b ON b.ReportID = a.ItemID
INNER JOIN dbo.Subscriptions c ON c.SubscriptionID = b.SubscriptionID
ORDER BY a.Path;

Colours are not displayed on SSRS subscriptions sent in mhtml format

August 26, 2015

Problem: Colours are not displayed on SSRS subscriptions sent in mhtml format

Solution: Rather than selecting the colour White for example from the color selection drop down list, instead select expression and enter the value =”White” This approach will also work when specifing hex codes eg enter =”#996699″ as an expression rather than #996699

user/login impersonation extending beyond the current database

August 3, 2015

A stored procedure utilizing execute as owner (database owner = sa) is unable to call an procedure within another database whose owner is also sa.

The reason for this is that security impersonation is only valid within the database that the impersonation is performed. Any calls external to the database will result in an error.

Consider the following scenario: 2 databases, 2 different owners in the same instance. db1 owner creates db2 owner as a user within db1. He then issues an EXCUTE AS USER = ‘db2_owner’ command. If external database calls were allowed he would have full admin to db2, clearly not desirable.

To extend impersonation beyond the database you need to let the instance know that teh dataabse is trustworthy.

You can do this: ALTER DATABASE xxx SET TRUSTWORTHY ON

see https://msdn.microsoft.com/en-us/library/ms188304(v=sql.105).aspx for further information

An INSERT EXEC statement cannot be nested

August 3, 2015

move the “insert into” from nest_1 inside of the dynamic sql – the idea is that you are hiding the insert into exec contained within the parent from the child.

USE test
GO

ALTER PROC nest_1
AS

CREATE TABLE #t (a INT)
INSERT INTO #t EXEC (‘ select 2 a’)

SELECT * FROM #t
GO

ALTER PROC nest_2
AS

CREATE TABLE #s (a INT)
INSERT INTO #s
EXEC (‘test..nest_1’)

SELECT * FROM #s
GO

exec nest_2