CODEWITHSUNDEEP
reporting-servicesssrs-2008
CJH
CJH

Reputation: 1594

SSRS - Users Table

I have done some digging but am unable to find out specific information about the Users table that is created in the ReportServer database?

Firstly I wanted to check what was the point of entry for users going into this table? In our table it looks like virtually ALL our domain logins exist in this table... Which leads me to the next question... Are these actually linked to the domain accounts at all (I presume not). I just want to make sure that if a domain account is disabled that any subscriptions 'Owned' by that user will not fail on the next run attempt.

Any help on this relatively dark area is greatly appreciated.

Regards Chris

Upvotes: 1

Views: 13326

Answers (2)

Sébastien Sevrin
Sébastien Sevrin

Reputation: 5405

Before answering let me remind you that the SSRS database is not officially documented, so the following answers are only be based on my experience of the product so they can be wrong / incomplete.

what was the point of entry for users going into this table?

  • The GetUserIDBySid and GetUserIDByName stored procedures, called by the GetUserID stored procedure.
  • The GetPrincipalID stored procedure, called by the UpdatePolicyPrincipal stored procedure.

The name can be confusing, because in fact these SPs are not only getting the User ID.
If the user does not exist in the database, they insert it.

Now an additional question should come up:

When are these SPs executed?

The obvious answer is "when SSRS needs to get the User ID".
This can be, for example, when:

  • Creating a policy for that specific user
  • The user execute a report
  • The user schedules a subscription

If the user is part of a group that has access to a report and has never done any action needing to get his User ID, he should not be present in the Users table.

Are these actually linked to the domain accounts at all

No, if you delete the account from your AD it will stay in the Users table.
The information that you can use if you need to link them for whatever reason are:

  • The User Login: UserName
  • The Security ID: Sid

Bonus question/answer:

What information does the Users table contains?

  • UserID: A generated GUID (NEWID())
  • Sid: The Security ID, if you need to find the Security ID from the User Login, you can use the SUSER_SID function
  • UserType: The UserType
  • AuthType: The LoginType
  • UserName: The NT User / Group login

Upvotes: 4

Coding Duchess
Coding Duchess

Reputation: 6919

Here is an excerpt from an article:

Users: This table contains details about access details for users of the report server including those users running the reports and those users publishing the reports.

SSRS keeps its own table of users and groups associated with its security, so even if the user account has been deleted from your Active Directory system, it will be available for you to reference inside the ReportServer database

This query below will show you to which report each user has access to:

SELECT u.UserName, r.RoleName, c.Path, c.Name 
FROM dbo.Users as u 
INNER JOIN dbo.PolicyUserRole as pr ON u.UserID=pr.UserID
INNER JOIN dbo.Roles as r on pr.RoleID = r.RoleID 
INNER JOIN dbo.Catalog as c on pr.PolicyID = c.PolicyID 
ORDER BY u.UserName

Upvotes: 1

Related Questions