query ReportServer database connection string user name

bukkybu 346 Reputation points
2021-04-28T20:00:57.05+00:00

I need to query the user name from SSRS shared datasource connection string. Any assistance is greatly appreciated.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,724 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,556 Reputation points
    2021-04-29T02:56:46.15+00:00

    Hi @bukkybu ,
    The ConnectionString in DataSources contains information about the embedded data source of the report. They are sensitive data, so they are encrypted; no official can decrypt them, even system administrators. What I mean is that the UserName you need to query is an encrypted existence.
    You could only view the creation of all shared data sources through the following statement:

    USE [ReportServer]  
    GO  
    SELECT Name  
    --, [ItemID] --Primary key  
    , [Path]  
    , [Description]  
    --, [CreatedByID] --need link to get anything usable from here  
    , Created.UserName as CreatedByUser  
    , [CreationDate]  
    --, [ModifiedByID] --need link to get anything usable from here  
    , Modified.UserName as ModifiedByUser  
    , [ModifiedDate]  
      
      
    FROM [dbo].[Catalog]  
      
    left join (select [UserID]  
                , [UserName]  
                from [dbo].[Users]) as Created  
            on Catalog.CreatedByID = Created.UserID  
    left join (select [UserID]  
                , [UserName]  
                from [dbo].[Users]) as Modified  
            on Catalog.ModifiedByID = Modified.UserID  
      
    WHERE [Type] = 5 -- 5 = Shared Datasource  
    ORDER BY [Path], Name  
    

    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful