Which user id is used in each data source?

Question

Using SQ: SSRS 2008 R2 - Beginner - Is there a report or query that provides which user ID is associated with the data source of each report? I need to be able to list out all the data sources being used and which user id is being used in the data source configuration instead of going to each report and using manage to review this. Any help is greatly appreciated

Answer 1

Hi ,

You'll be able to find this information from report server database table.

1.Go to server where SSRS is deploy

2.Connect to SQL Server Management Studio.

3.You can use below query on REPORTSERVER database;

Useful table in your scenario.

select * from DataSource

Thanks

---

Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

Answer 2

Thank you - This is useful - but the username column is encrypted - how do I unencrypt

Answer 3

Thank you - This is useful - but the username column is encrypted - how do I unencrypt

as per my knowledge there is no way to decrypt it and below link confirm my understanding :)but there is work around also that have shared on technet;

https://social.msdn.microsoft.com/Forums/en-US/f65b4d50-abaa-400b-a09d-ae1d7d1ea041/decrypt-the-connectionstring?forum=sqlreportingservices

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause. 
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource' 
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' 
     AS rd) 
,SDS AS 
    (SELECT SDS.name AS SharedDsName 
           ,SDS.[Path] 
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF 
     FROM dbo.[Catalog] AS SDS 
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource 
 
SELECT CON.[Path] 
      ,CON.SharedDsName 
      ,CON.ConnString 
FROM 
    (SELECT SDS.[Path] 
           ,SDS.SharedDsName 
           ,DSN.value('ConnectString[1]', 'varchar(200)') AS ConnString 
     FROM SDS 
          CROSS APPLY  
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN) 
     ) AS CON 
-- Optional filter: 
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%' 
ORDER BY CON.[Path] 
        ,CON.SharedDsName;

Thanks

---

Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem. http://techequation.com

Answer 4

Hi RudyCat,

As PrajapatiNeha mentioned that we can find the username in the DataSource Table, but currently it is impossible to decrypt the username.

I recommend you to using manage to review this.

Thanks,
Swallow